Thread: subtotals (2002)
2004-08-01, 04:56 #1
- Join Date
- Aug 2003
- Voorhees, New Jersey
- Thanked 0 Times in 0 Posts
what's the best way to get subtotals when the column uf numbers is interrupted by blank rows?
(The blank rows correspond to "major" headings for which I would like to have subtotals.)
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2004-08-01, 08:15 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 313 Times in 307 Posts
Re: subtotals (2002)
I am not completely sure I understand your setup.
1) Have you tried using the "built-in" data-subtotals feature in excel? it will place subtotals automatically between the "categories" that you pick.
But to try and answer your question. If I understand it:
Let's assume you have numbers in column A, and a header in row 1 some of the rows in column A are blank to indicated where you want subtotals.
I will assume you want the subtotals in column B, and you want to total col a from the row above the blank to the previous blank row.
Put this formula in B2:
This can be copied down the rows
So, if col A is not blank you get a null string. If it is blank it sums the values from the first row to the current row then subtracts the sum of the row above the current row to the first row, giveing you the subtotal of the rows above.
If you want the subtotal to sum the values in column A for the rows below th blank row and to the next blank row (ie the subtotal as a "heading" above the data) you can use this formula (change the A100 and B100 to the last row)
The logic is the same only it sums from the bottom instead of the top.
In either case change the column letters as desired.