Hi Edward

Try
=SUMPRODUCT(($A$2:$A$1000=A2)*$B$2:$B$1000)

--
Regards

Roger Govier


"Edward" wrote in message
ups.com...
This resolves the A3, A4 problem but now the formula does not work. I
get the same result if I leave out the INDIRECT and just go with
tree=A2.

On Jul 9, 10:13 am, "Roger Govier"
wrote:
Hi Edward

Try using
tree=INDIRECT("A"&ROW(A2))
That will resolve to INDIRECT(A2) and change as you copy down to A3,
A4
etc.

--
Regards

Roger Govier

"Edward" wrote in message

ps.com...



Is there a way to assign the criteria for DSUM (or a similar
funciton)
without giving a range cells but a string literal or formula? I
came
up with something that works a little bit but is still having
problems:

A | B |
C | D
1 | tree | height | sum by type |
criteria
2 | apple | 1 | =DSUM(A$1:B$5,"height",D1:D2) |
=tree=INDIRECT("A2")
3 | pear | 2 | =DSUM(A$1:B$5,"height",D2:D3) |
=tree=INDIRECT("A3")
4 | apple | 3 | =DSUM(A$1:B$5,"height",D3:D4) |
=tree=INDIRECT("A4")
5 | fig | 4 | =DSUM(A$1:B$5,"height",D4:D5) |
=tree=INDIRECT("A5")

result:
A | B |
C | D
1 | tree | height | sum by type |
criteria
2 | apple | 1 |
5 | #NAME?
3 | pear | 2 |
2 | #NAME?
4 | apple | 3 |
5 | #NAME?
5 | fig | 4 |
8 | #NAME?

This does what I want (sums tree heights by type in column D) but
has
the problem that I cannot simply copy and paste the formula in D2
to
successive rows without manually changing the "A2" to "A3", etc.-
Hide quoted text -

- Show quoted text -