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.


"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,
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

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

=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

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



.

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



.

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



.



.

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.

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.