ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sumif for unique values (https://www.excelbanter.com/excel-worksheet-functions/260775-sumif-unique-values.html)

Graham H[_2_]
Sumif for unique values
 
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
Sumif for unique values
 
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" wrote:

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 H[_3_]
Sumif for unique values
 
Mike,
You are an absolute star! Many thanks for that.

Graham

"Mike H" wrote:

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" wrote:

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
Sumif for unique values
 
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.


"Graham H" wrote:

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

Graham

"Mike H" wrote:

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" wrote:

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

T. Valko
Sumif for unique values
 
=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" wrote in message
...
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" wrote:

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
Sumif for unique values
 
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:

=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" wrote in message
...
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" wrote:

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

Graham

On 06/04/2010 16:24, T. Valko wrote:
=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.

T. Valko
Sumif for unique values
 
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" wrote in message
...
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:

=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" wrote in message
...
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" wrote:

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



.

T. Valko
Sumif for unique values
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Graham

On 06/04/2010 16:24, T. Valko wrote:
=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.

Mike H
Sumif for unique values
 
Other than counting uniques with no condition, like:
=SUMPRODUCT((range<"")/COUNTIF(range,range&""))

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.


"T. Valko" wrote:

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" wrote in message
...
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:

=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" wrote in message
...
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" wrote:

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



.



.

T. Valko
Sumif for unique values
 
He'll catch you posting 'THAT' formula!!

I'm not afraid! <g

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Other than counting uniques with no condition, like:
=SUMPRODUCT((range<"")/COUNTIF(range,range&""))

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.


"T. Valko" wrote:

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" wrote in message
...
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:

=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" wrote in message
...
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" wrote:

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



.



.


All times are GMT +1. The time now is 07:18 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com