How to get a subtotal from a varying number of rows?

**Mark53** · 06-03-2013, 07:14 AM

Hi, people.
If the data looks sorta' like this...

24 56 54
12 43 54
34 65 73
Subtotal 70 164 181

2 56 23
34 76 29
23 8 63
34 7 53
54 2 76
Subtotal 147 149 244

43 22 77
13 6 4
Subtotal 56 28 81

There could be from 2 to 20 rows of data between each subtotal row.
How can I add up the data From AFTER the previous "subtotal" line?

And then...
is there a way to get a Grand Total at the bottom,
just adding up the numbers in the "subtotal" lines?

Thanks very much.

Mark53

**:) Sixthsense :)** · 06-03-2013, 07:18 AM

Please attach a sample workbook with expected output for better understanding

**Roger Govier** · 06-03-2013, 08:37 AM

Hi

Take a look at the attached file.
Sheet 1 shows how you can use Excel's built in SubTotal feature within Outlines on the Data tab, if you have a criterion upon which to cause the subtotal to appear.
In the example I have posted there is a change in date, and the graphic on the sheet shows how this was selected in the Subtotal

On sheet 2, I have create a formula based solution, which depends upon you writing the word Subtotal in column A, at the point where you insert my subtotaling formula

=SUMPRODUCT(--($A$2:$A4<>"Subtotal"),B$2:B4)-SUMPRODUCT(--($A$2:$A4="Subtotal"),B$2:B4)