E
Edward
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",D12) |
=tree=INDIRECT("A2")
3 | pear | 2 | =DSUM(A$1:B$5,"height",D23) |
=tree=INDIRECT("A3")
4 | apple | 3 | =DSUM(A$1:B$5,"height",D34) |
=tree=INDIRECT("A4")
5 | fig | 4 | =DSUM(A$1:B$5,"height",D45) |
=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.
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",D12) |
=tree=INDIRECT("A2")
3 | pear | 2 | =DSUM(A$1:B$5,"height",D23) |
=tree=INDIRECT("A3")
4 | apple | 3 | =DSUM(A$1:B$5,"height",D34) |
=tree=INDIRECT("A4")
5 | fig | 4 | =DSUM(A$1:B$5,"height",D45) |
=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.