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



.



.