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.

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.

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 -

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 -