Sumif for unique values

H@discussions.microsoft.com Graham H

unread,

Apr 6, 2010, 6:39:01 AM4/6/10

to

I have two columns where basically I want to sum all the unique values in
column B i.e the 5.89 etc based on the criteria of which number they are in
Column A which will always be sorted in order 1 to a variable number ie it
can be 3, or up to 30.So the summary I am after is e.g

Sheet Total Land
1 27.5
2 31.92
etc

I would prefer in this situation to avoid pivot tables
I would appreciate any help.
Graham
Sheet Total Land
(D)
1 5.89
1 5.89
1 1.34
1 20.27
1 20.27
1 20.27
1 20.27
1 20.27
1 20.27
1 20.27
1 20.27
1 20.27
2 12.36
2 12.36
2 0.74
2 0.74
2 5.2
2 5.2
2 13.62
2 13.62
2 13.62
2 13.62
2 13.62
2 13.62
3 9.3
3 9.3
3 11.21
3 11.21
3 11.21
3 14.39
3 14.39
3 14.39
3 7.87
3 7.87
3 7.87
3 7.87
4 8.81
4 8.81
4 8.81
4 8.81
4 8.81
4 12.84
4 12.84

Mike H

unread,

Apr 6, 2010, 7:01:02 AM4/6/10

to

Graham,

Try this

=SUMPRODUCT(($A$1:$A$43=ROW(A1))/COUNTIF($B$1:$B$43,$B$1:$B$43&"")*$B$1:$B$43)

the formula as posted does the 1's in col A drag down for 2 etc
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Graham H

unread,

Apr 6, 2010, 7:17:01 AM4/6/10

to

Mike,
You are an absolute star! Many thanks for that.

Graham

Mike H

unread,

Apr 6, 2010, 7:21:01 AM4/6/10

to

Your welcome and thanks for the feedback

T. Valko

unread,

Apr 6, 2010, 11:24:26 AM4/6/10

to

>=SUMPRODUCT(($A$1:$A$43=ROW(A1))/COUNTIF($B$1:$B$43,$B$1:$B$43&"")*$B$1:$B$43)

The OP seems to be satisfied but that formula could return an incorrect
result if a number to sum is the same for more than one criteria.

1...10
1...10
1...12
2...10

=SUMPRODUCT((A2:A5=1)/COUNTIF(B2:B5,B2:B5&"")*B2:B5)

=18.667

The correct result should be 22.

Try this array formula**. Assuming no empty cells in column B.

=SUM(IF(FREQUENCY(IF(A2:A5=1,MATCH(B2:B5,B2:B5,0)),ROW(B2:B5)-ROW(B2)+1),B2:B5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

"Mike H" <Mi...@discussions.microsoft.com> wrote in message
news:1538528A-7CF4-4DB2...@microsoft.com...

Mike H

unread,

Apr 6, 2010, 12:22:01 PM4/6/10

to

Biff,

Thanks for that, i tested it on the OP's data and never considered that
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"T. Valko" wrote:

> .
>

Graham

unread,

Apr 6, 2010, 1:54:36 PM4/6/10

to

Many thanks for that and sorry for the second post, I had not seen the
post before I sent it. Thanks again

Graham

T. Valko

unread,

Apr 6, 2010, 2:33:21 PM4/6/10

to

Other than counting uniques with no condition, like:

=SUMPRODUCT((range<>"")/COUNTIF(range,range&""))

SUMPRODUCT is not very easy to use when the uniques are conditional. I've
seen some attempts but IMHO the SUM(FREQUENCY method is better.

--
Biff
Microsoft Excel MVP

"Mike H" <Mi...@discussions.microsoft.com> wrote in message

news:962BF906-2C7B-49B9...@microsoft.com...

T. Valko

unread,

Apr 6, 2010, 2:34:27 PM4/6/10

to

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Graham" <gra...@haughs.orangehome.co.uk> wrote in message
news:eG%23F6Hb1...@TK2MSFTNGP06.phx.gbl...

Mike H

unread,

Apr 6, 2010, 5:49:01 PM4/6/10

to

> Other than counting uniques with no condition, like:
> =SUMPRODUCT((range<>"")/COUNTIF(range,range&""))

He'll catch you posting 'THAT' formula!!
--
Mike

"T. Valko" wrote:

> .
>

T. Valko

unread,

Apr 6, 2010, 6:43:42 PM4/6/10

to

>He'll catch you posting 'THAT' formula!!

I'm not afraid! <g>

--
Biff
Microsoft Excel MVP

"Mike H" <Mi...@discussions.microsoft.com> wrote in message

news:684E173B-2F4E-489B...@microsoft.com...