subtotals (2002)

[tfboni]

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.)

[sdckapr]

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:

<pre>=IF(ISBLANK(A2),SUBTOTAL(9,A$2:A2)-SUM(B$1:B1),"")</pre>


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)

<pre>=IF(ISBLANK(A2),SUBTOTAL(9,A2:A$100)-SUM(B3:B$100),"")</pre>


The logic is the same only it sums from the bottom instead of the top.
In either case change the column letters as desired.

Steve