Dynamic Sum formula

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
486
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,515
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Mar 6, 2020
Messages
486
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows

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

1599377162710.png


With my suggestion, such an inserted row is included.

1599377277750.png


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
Joined
Mar 6, 2020
Messages
486
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,515
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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