Sum from previous sum formula in a column to active cell

[rrenis]

Hi all, does anyone know how if you can automatically populate an active cell with a sum formula that will total the cells between the last instance of a sum formula in a column above the active cell and the active cell itself? I currently just use the auto sum on the ribbon but the unfortunately manner in which the sheets are formatted means that there are gaps between figures so I always end up manually dragging the range.

To demonstrate (albeit very simplistically) the sheet would appear something like this (where the text appears in column B and the total in column G)...

Text A Total A
Blank Row
Text B Total B
Blank Row
Sub-Total
Blank Row
Text C Total C
Blank Row
Text D Total D
Blank Row
Text E Total E
Blank Row
Sub-Total (VBA to insert Sub-Total of total C, D and E here)

To further complicate things there are rows beneath the active cell which are populated so the code would need to offset up from the active cell rather than find the last used cell in the column. Also sometimes there is more than 1 blank row between totals).

If anyone has any idea how to achieve this I'd be very grateful. Thanks in advance!

Cheers,
rrenis.

[Kenneth Hobs]

You say Subtotal but did you use =Subtotal()?

[rrenis]

Hi Ken, many thanks for the reply. Apologies for not getting back to you sooner but I was away from a PC yesterday. I've been using =sum() in these situations in the past and must admit to never using the =sumtotal() before.
I've just tried =sumtotal() as I've just got into the office but it does not seem to work. I'll have more of a look into it though as =sumtotal() is something I'm new to - thanks for the suggestion

[Bob Phillips]

It should be SUBTOTAL not SUMTOTAL, but post your woirkbook and let's see what the data really looks like.

[rrenis]

Hi xld, I've attached a stripped back version of the sheet I use to put together initial costs for projects and added some comment boxes to clarify aspects of the sheet. These are often completed quickly under tight deadlines and last week I missed out a sub-total in a latter calculation on the sheet which got me thinking about the need to have a few checks built in to prompt for any potential errors! The sheet can grow far in excess of the attached (based upon each individual project) with items being inserted as more information becomes available.

I've taught myself excel (and so have gaps in my knowledge =subtotal() being a prime example!) and with the help of this forum understand aspects of VBA which has been invaluable. So I'm hoping either what I'm proposing is feasible with a bit of help (or any advice if I'm coming at it from the wrong angle). I've had a look at =Subtotal() and the way it groups items and adds additional text leads me to think that it's perhaps not an option?

Thanks again!

Basic Example.xlsm

[Kenneth Hobs]

It looks like you have a custom need. If you can post a short before and after sheet, we could see what you want more clearly.

I suspect that if your before sheet has the word "sub-total" in Column C, then the code would add the =subtotal formula with the range determined between single bottom line formatted cells above it. If this is the case, we would not need the before and after sheet example. Of course if the designer would just use =subtotal when the "sub-total" word is added in Column C, that should solve the problem.

For the total of the subtotals, one uses the whole range. The =subtotal values would not be added. Of course =subtotal does sum the cells where =sum is used which is why =subtotal is the better route for you.

[Bob Phillips]

This array formula is a tad clunky, but it works (doesn't catch your missing Sub-total though

=IF(C12="Sub-total",SUMPRODUCT(INDEX($D$1:$D11,MAX(IF($C$1:$C11="Sub-total",ROW($C$2:$C12)))):$D11,INDEX($F$1:$F11,MAX(IF($C$1:$C11="Sub-total",ROW($C$2:$C12)))):$F11),"")

[rrenis]

Hi Ken and xld, thanks for the replies!

xld, I've tried the array formula and I'm getting a #value error but I'll keep looking at it and incorporate it into the sheet once I figure out what I'm doing wrong.

Ken, you're assumption that column C has the text 'Sub-Total' in and the code would add the =subtotal() formula is correct. It's just I'm not sure how to look for cells above the active cell (i.e. the next subtotal required) and determine the range required. Perhaps the code could offset to column C and look up to find the next instance of 'Sub-Total' text and determine the row from that - would that be easier or again are things getting overly complicated?

Thanks again for your time

[Bob Phillips]

Did you array-enter it (Ctrl-Shift-Enter)?

[rrenis]

Doh!!!
It's now working great! Thanks xld