|
DSUM criteria
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. |
DSUM criteria
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. |
DSUM criteria
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 - |
DSUM criteria
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 - |
| All times are GMT +1. The time now is 10:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com