Sum values below blank cell

[Marcia]

Hi all. I have a spreadsheet that needs a subtotal of values below a blank cell. Is there a single formula that I could use to get the subtotals by filtering the cells where the subtotals are and pressing Ctrl+Enter? I would like to use =SUBTOTAL(109) instead of the SUM function to get the totals.
Attached is a sample table. The real table contains around 3,800 subtotals.


Thank you.

[Pecoflyer]

I don't quite understand your query

[Marcia]

Originally Posted by Pecoflyer

I don't quite understand your query

Okay. In the attached sample table, if I select C8:C35, what is the formula that would get the subtotal of all values above the formula until the blank cells? That after entering the formula, I press Ctrl+Enter to apply the code in all the selected cells where the subtotals should be?
Thank you.

[ArviLaanemets]

Instead of copy of paper table from 18.-19. century, use a table with structure like
Name, Amount1, Amount22, Amount222
or even better
Name, WhateverParameter, Amount

Then you can simply create a pivot table which will give you any totals and subtotals you need.

[Pecoflyer]

I still don't get it. Could you please fill in some desired results manually. Thx

[Marcia]

Originally Posted by Pecoflyer

I still don't get it. Could you please fill in some desired results manually. Thx

Attached is the desired result in another table. Thank you.

[Pecoflyer]

Would using some extra columns help?

[Pecoflyer]

Any success?

[Marcia]

Originally Posted by Pecoflyer

Any success?

I'm sorry. I had been swamped by other tasks. Adding helper columns would mess the table. Thank you for the suggestion. I could use it in other data problems.

[ArviLaanemets]

Why don't do it as in added example instead?

The totals calculation doesn't depend on order the data are entered into tData Table. I.e. there is no need for entered data to be sorted in any way (e.g. by Group);
To add new groups later, you simply add a new row into tTotals Table with new group value, and after that you can add any number entries for new group into tData Table - without any need to redesign of any formulas.

In tData Table, you can hide the Group column, in case you want this.
I freezed the rows until the header row of tData table, so this header and tTotals Table will be always visible.

(Btw. You can define the Group column of tTotals Table as Named Range, and use it as the source of Data Validation List for Group column in tData Table)

[Marcia]

Thank you Arvil. Yours is another idea and approach that I could use later.