Sumif for unique values

  • Thread starter Thread starter Graham H
  • Start date Start date
G

Graham H

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
 
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.
 
Your welcome and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
=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,

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.
 
Many thanks for that and sorry for the second post, I had not seen the
post before I sent it. Thanks again

Graham
 
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.
 
Other than counting uniques with no condition, like:
=SUMPRODUCT((range<>"")/COUNTIF(range,range&""))
Click to expand...

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

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