Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Subtotals
I need to sum the subtotals in a field, and it appears the best way to do it
is to add all of the cells that are above a blank cell. For example: A 1 <FORMULA 2 <heading 3 5 4 7 5 12 6 7 2 8 3 9 5 10 The subtotals are in rows 5 and 9. I need to sum those (and all other subtotals in this field) in cell A1. It looks like the best way to do this would be to condition the formula to sum all cells above the blank cells (cells A5 and A9--and all other cells above blanks). How would I do this? I searched for an example of this on the web and in this forum but haven't found anything on it. Thanks you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Subtotals
If you used the SUBTOTAL function to calculate the subtotals
then you can use the SUBTOTAL function at the bottom of the list. (The SUBTOTAL function ignores other SUBTOTAL functions that are in its referenced range.) So....if your data is in A2:A100 with SUBTOTAL function interspersed in that range, this formula will return the correct Grand Total A101: =SUBTOTAL(9,A2:A100) Note: the 9 causes the function to return the sum Other first parameter options Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP Alternatively, if you used the SUM function in *every* place a subtotal should be (even if it's totaling just one item, the you can use this A101: SUM(A2:A100)/2 But, I'd go with the SUBTOTAL function, if practical Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Exceller" wrote: I need to sum the subtotals in a field, and it appears the best way to do it is to add all of the cells that are above a blank cell. For example: A 1 <FORMULA 2 <heading 3 5 4 7 5 12 6 7 2 8 3 9 5 10 The subtotals are in rows 5 and 9. I need to sum those (and all other subtotals in this field) in cell A1. It looks like the best way to do this would be to condition the formula to sum all cells above the blank cells (cells A5 and A9--and all other cells above blanks). How would I do this? I searched for an example of this on the web and in this forum but haven't found anything on it. Thanks you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Subtotals
Thanks, Ron, but the formulas that I'm using to subtotal within that field
don't use the SUBTOTAL function. In fact, the formula used to subtotal ranges in that field is the (brilliant) Index/Match formula you came up with for me last week (thanks again)! So, I think I need something that sums each cell above a blank cell. Thanks. "Ron Coderre" wrote: If you used the SUBTOTAL function to calculate the subtotals then you can use the SUBTOTAL function at the bottom of the list. (The SUBTOTAL function ignores other SUBTOTAL functions that are in its referenced range.) So....if your data is in A2:A100 with SUBTOTAL function interspersed in that range, this formula will return the correct Grand Total A101: =SUBTOTAL(9,A2:A100) Note: the 9 causes the function to return the sum Other first parameter options Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP Alternatively, if you used the SUM function in *every* place a subtotal should be (even if it's totaling just one item, the you can use this A101: SUM(A2:A100)/2 But, I'd go with the SUBTOTAL function, if practical Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Exceller" wrote: I need to sum the subtotals in a field, and it appears the best way to do it is to add all of the cells that are above a blank cell. For example: A 1 <FORMULA 2 <heading 3 5 4 7 5 12 6 7 2 8 3 9 5 10 The subtotals are in rows 5 and 9. I need to sum those (and all other subtotals in this field) in cell A1. It looks like the best way to do this would be to condition the formula to sum all cells above the blank cells (cells A5 and A9--and all other cells above blanks). How would I do this? I searched for an example of this on the web and in this forum but haven't found anything on it. Thanks you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Subtotals
Try this:
Using your posted example A 1 <FORMULA 2 <heading 3 5 4 7 5 12 6 7 2 8 3 9 5 10 Then... A11: =SUMIF(A4:A10,"",A3:A9) Notice the staggered ranges A4:A10 vs A3:A9 Does that help? *********** Regards, Ron XL2002, WinXP "Exceller" wrote: Thanks, Ron, but the formulas that I'm using to subtotal within that field don't use the SUBTOTAL function. In fact, the formula used to subtotal ranges in that field is the (brilliant) Index/Match formula you came up with for me last week (thanks again)! So, I think I need something that sums each cell above a blank cell. Thanks. "Ron Coderre" wrote: If you used the SUBTOTAL function to calculate the subtotals then you can use the SUBTOTAL function at the bottom of the list. (The SUBTOTAL function ignores other SUBTOTAL functions that are in its referenced range.) So....if your data is in A2:A100 with SUBTOTAL function interspersed in that range, this formula will return the correct Grand Total A101: =SUBTOTAL(9,A2:A100) Note: the 9 causes the function to return the sum Other first parameter options Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP Alternatively, if you used the SUM function in *every* place a subtotal should be (even if it's totaling just one item, the you can use this A101: SUM(A2:A100)/2 But, I'd go with the SUBTOTAL function, if practical Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Exceller" wrote: I need to sum the subtotals in a field, and it appears the best way to do it is to add all of the cells that are above a blank cell. For example: A 1 <FORMULA 2 <heading 3 5 4 7 5 12 6 7 2 8 3 9 5 10 The subtotals are in rows 5 and 9. I need to sum those (and all other subtotals in this field) in cell A1. It looks like the best way to do this would be to condition the formula to sum all cells above the blank cells (cells A5 and A9--and all other cells above blanks). How would I do this? I searched for an example of this on the web and in this forum but haven't found anything on it. Thanks you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Subtotals
Ron,
Sorry to get back with you so late. We had a blizzard here yesterday (Columbus, OH) and I left the office early. It works perfectly! Thanks so much for your help. Regards, Exceller "Ron Coderre" wrote: Try this: Using your posted example A 1 <FORMULA 2 <heading 3 5 4 7 5 12 6 7 2 8 3 9 5 10 Then... A11: =SUMIF(A4:A10,"",A3:A9) Notice the staggered ranges A4:A10 vs A3:A9 Does that help? *********** Regards, Ron XL2002, WinXP "Exceller" wrote: Thanks, Ron, but the formulas that I'm using to subtotal within that field don't use the SUBTOTAL function. In fact, the formula used to subtotal ranges in that field is the (brilliant) Index/Match formula you came up with for me last week (thanks again)! So, I think I need something that sums each cell above a blank cell. Thanks. "Ron Coderre" wrote: If you used the SUBTOTAL function to calculate the subtotals then you can use the SUBTOTAL function at the bottom of the list. (The SUBTOTAL function ignores other SUBTOTAL functions that are in its referenced range.) So....if your data is in A2:A100 with SUBTOTAL function interspersed in that range, this formula will return the correct Grand Total A101: =SUBTOTAL(9,A2:A100) Note: the 9 causes the function to return the sum Other first parameter options Num Function 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP Alternatively, if you used the SUM function in *every* place a subtotal should be (even if it's totaling just one item, the you can use this A101: SUM(A2:A100)/2 But, I'd go with the SUBTOTAL function, if practical Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Exceller" wrote: I need to sum the subtotals in a field, and it appears the best way to do it is to add all of the cells that are above a blank cell. For example: A 1 <FORMULA 2 <heading 3 5 4 7 5 12 6 7 2 8 3 9 5 10 The subtotals are in rows 5 and 9. I need to sum those (and all other subtotals in this field) in cell A1. It looks like the best way to do this would be to condition the formula to sum all cells above the blank cells (cells A5 and A9--and all other cells above blanks). How would I do this? I searched for an example of this on the web and in this forum but haven't found anything on it. Thanks you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals: Nested subtotals below higher subtotal | Excel Discussion (Misc queries) | |||
summing subtotals | Excel Worksheet Functions | |||
How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |