Dynamic Sum formula

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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))
 
Upvote 0
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)
 
Upvote 0
Thanks Peter and Dave, tried both solutions and they worked out. Appreciate the quick turnaround and can now enjoy Father's Day.
 
Upvote 0
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)
 
Upvote 0
Hi again Peter, your original interpretation was correct, I tried a couple of different scenarios, I might have gone off a bit prematurely.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top