# Dynamic Sum formula

#### Tigerexcel

##### Active Member
I'm after a formula that will sum the values above until it reaches a text entry.

Book14
BCD
1Subject
2JackMary
3HistoryUnitsUnits
4125
585
6Total2010
7ScienceUnitsUnits
884
9106
10158
1152
12Total3820
Sheet2
Cell Formulas
RangeFormula
C6:D6C6
[]

A number of people who don't use Excel regularly have to use the spreadsheet and will need to enter/delete items accordingly. To ensure that it calculates properly I'd like the formula to add values above it until it reaches a word, in this case Units
I thought it could be done through OFFSET but couldn't get it to work. B12 should return 38, B6 should return 20 at the moment but the formula needs to be able to add everything from the Total row until it finds Units which will change depending on how many rows are added/deleted for each Subject category.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Peter_SSs

##### MrExcel MVP, Moderator
Try this in C6 copied to the other Total cells.

=SUM(C\$1:OFFSET(C6,-1,,1))-2*SUMIF(\$B\$1:OFFSET(\$B5,-1,,1),"Total",C\$1:OFFSET(C6,-1,,1))

#### Dave Patton

##### Well-known Member
Try using Data Subtotal

Initial information
T202009a.xlsm
BCD
2JackMary
3SubjectUnitsUnits
4History125
5History85
6Science84
7Science106
8Science158
9Science52
1e

With Data Subtotal

T202009a.xlsm
BCD
1
2JackMary
3SubjectUnitsUnits
4History125
5History85
6History Total2010
7Science84
8Science106
9Science158
10Science52
11Science Total3820
12Grand Total5830
1e
Cell Formulas
RangeFormula
C6:D6C6=SUBTOTAL(9,C4:C5)
C11:D11C11=SUBTOTAL(9,C7:C10)
C12:D12C12=SUBTOTAL(9,C4:C10)

#### Tigerexcel

##### Active Member
Thanks Peter and Dave, tried both solutions and they worked out. Appreciate the quick turnaround and can now enjoy Father's Day.

#### Peter_SSs

##### MrExcel MVP, Moderator

ADVERTISEMENT

tried both solutions and they worked out.
If they both work then I did not interpret the question correctly. I thought that you were saying that the user might insert data anywhere. With Dave's suggestion, if a row is inserted as shown below (green) then the inserted value is not included in the subsequent total below

With my suggestion, such an inserted row is included.

If you do not need to allow for rows inserted immediately above a total row then my suggestion, without needing to fill all rows in the Subject column becomes ..

20 09 05.xlsm
BCD
1Subject
2JackMary
3HistoryUnitsUnits
4125
585
6Total2010
7ScienceUnitsUnits
884
9106
10158
1152
12Total3820
Sum
Cell Formulas
RangeFormula
C6:D6,C12:D12C6=SUM(C\$1:C5)-2*SUMIF(\$B\$1:B5,"Total",C\$1:C5)

#### Tigerexcel

##### Active Member
Hi again Peter, your original interpretation was correct, I tried a couple of different scenarios, I might have gone off a bit prematurely.

#### jasonb75

##### Well-known Member
Subtotal works with inserted rows if you include the header and formula cell in the range. Note that as the formula cell will create a circular reference you will need to enable iterative calculation in excel options.
Book1
BCD
1Subject
2JackMary
3HistoryUnitsUnits
4125
585
6Total2010
7ScienceUnitsUnits
884
9106
10158
1152
12Total3820
Sheet2
Cell Formulas
RangeFormula
C6:D6C6=SUBTOTAL(9,C\$3:C6)
C12:D12C12=SUBTOTAL(9,C\$7:C12)

#### Dave Patton

##### Well-known Member
The Subtotal can be useful; see references below.
With the data recorded with full information on each line, Pivot Tables and other alternatives
can prepare the summaries and reports.
You can use the alternative that best fits your preferences and requirements.

N.B. I did not enter the formulas; I used Data Subtotal (from the Outline area)
See Microsoft or Contextures for additional information.

Replies
3
Views
68
Replies
12
Views
429
Replies
4
Views
133
Replies
0
Views
72
Replies
4
Views
104

Threads
1,118,990
Messages
5,575,397
Members
412,661
Latest member
joelgibney1