Subtotaling sums at inconsistent intervals.

RodneyC

Active Member
Joined
Nov 4, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
I have a formulas similar to theses in several places up and down my sheet

In D8 I have =SUM(D3:D7)
In D17 I have =SUM(D9:D16)
In D27 I have =SUM(D18:D26)
In D33 I have =SUM(D28:D32)

D3 - D7, etc. each have either a typed in value or a value generated by a formula

There's a lot of them going down to about row 700 and there's no normalcy or pattern to determine how many of the lines above need to be totaled. It's set by groups listed in column B. Sometimes there will be 2 or 3 in a group, sometimes there may be 7, 8 or 11 or maybe only 1.

Rather than manually typing in the formula in the 85 or so cells it needs to go in, is there another way to do this? Cells C8, C17, C27, C33 etc. are all empty so I could put TOTAL or something else there to help Identify where the break is if needed.

Thanks in advance
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You could create a formula that considers what is in Col B or C. You mentioned something about groups in Col B. What do they look like?
 
Upvote 0
Column B consists of City names. Column C are street names within a City. Example, B3:B7 all say Abilene. C3 has 1st St C4 has 2nd St and so on. The groups in B will always have the same value. Each line in C will be different. The exceptions to C are that currently C8, C17, etc. (those at the end of each group, are all empty.
 
Upvote 0
Would we find any particular city anywhere else in column B other than the local group where it forms a contiguous range?
 
Upvote 0
Thanks. An IF(look for blank,SUMIF construction should work. I’m away from my computer at the moment, but will post back shortly.
 
Upvote 0
Here is one idea using a formula. The issue with trying to include a formula in col D for summing values in col D is that a circular reference error will be created. This uses an IF function to look for the blank in col C, then uses the contents of col B in the row immediately above...and feeds that into a SUMIF to find all of the col B entries that match the same city. Then any corresponding values in col D are summed. The IF statement then returns either the sum or the original value in col D...and everything is placed in col E.
Book2
BCDE
1CityStreetOrig AmtOrig and Subtotals
2Abilene123 Main11
3Abilene124 Main22
4Abilene125 Main33
5Abilene126 Main44
6Abilene127 Main1010
7Abilene128 Main1111
831
9Chicago32 81st Ave44
10Chicago33 81st Ave55
11Chicago34 81st Ave66
1215
13Denver1 Main88
14Denver2 Main77
15Denver3 Main66
16Denver4 Main55
1726
18Reno33 Main1313
Sheet1
Cell Formulas
RangeFormula
E2:E18E2=IF(ISBLANK($C2),SUMIF($B:$B,$B1,$D:$D),D2)
 
Upvote 0
Here is one idea using a formula. The issue with trying to include a formula in col D for summing values in col D is that a circular reference error will be created. This uses an IF function to look for the blank in col C, then uses the contents of col B in the row immediately above...and feeds that into a SUMIF to find all of the col B entries that match the same city. Then any corresponding values in col D are summed. The IF statement then returns either the sum or the original value in col D...and everything is placed in col E.
Book2
BCDE
1CityStreetOrig AmtOrig and Subtotals
2Abilene123 Main11
3Abilene124 Main22
4Abilene125 Main33
5Abilene126 Main44
6Abilene127 Main1010
7Abilene128 Main1111
831
9Chicago32 81st Ave44
10Chicago33 81st Ave55
11Chicago34 81st Ave66
1215
13Denver1 Main88
14Denver2 Main77
15Denver3 Main66
16Denver4 Main55
1726
18Reno33 Main1313
Sheet1
Cell Formulas
RangeFormula
E2:E18E2=IF(ISBLANK($C2),SUMIF($B:$B,$B1,$D:$D),D2)
Thanks for your continued help here. I was handed this project late yesterday and early this morning the project "evolved" and this portion isn't needed. In any case, I have a potential solution if the challenge comes up again. THANKS
 
Upvote 0
Ahh...I've encountered quite a few of those "evolutions." Best of luck!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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