SOLVED

Sum of variable number of rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2512725%22%20slang%3D%22en-US%22%3ESum%20of%20variable%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2512725%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20would%20like%20to%20find%20a%20formula%20that%20will%20take%20the%20total%20of%20a%20column%20--%20but%20the%20number%20of%20rows%20in%20that%20column%20will%20vary.%26nbsp%3B%20The%20attachment%20hopefully%20helps%20to%20visualize%20how%20the%20totals%20should%20be%20grouped%20together.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESum%20hours%201%20(column%20F)%3A%26nbsp%3B%20this%20is%20the%20total%20of%20column%20D%2C%20starting%20from%20the%20row%20that%20has%20a%20value%20in%20column%20B%2C%20through%20the%20next%20row%20with%20a%20value%20in%20column%20C.%26nbsp%3B%20The%20next%20sum%20starts%20with%20the%20next%20row%20that%20has%20a%20value%20in%20Column%20B.%26nbsp%3B%20In%20column%20F%2C%20the%20sum%20is%20populated%20to%20the%20row%20with%20a%20value%20in%20column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESum%20hours%202%20(column%20G)%3A%26nbsp%3B%26nbsp%3Bthis%20is%20the%20total%20of%20column%20E%2C%20starting%20from%20the%20row%20that%20has%20a%20value%20in%20column%20B%2C%20through%20the%20next%20row%20with%20a%20value%20in%20column%20C.%26nbsp%3B%20%26nbsp%3BThe%20next%20sum%20starts%20with%20the%20next%20row%20that%20has%20a%20value%20in%20Column%20C.%26nbsp%3B%20In%20column%20G%2C%20the%20sum%20is%20populated%20to%20the%20row%20with%20a%20value%20in%20column%20C.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2512725%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513110%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20variable%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1094487%22%20target%3D%22_blank%22%3E%40richzip1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EKind%20reminder%26nbsp%3B%3C%2FSPAN%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CSPAN%3Efrom%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_blank%22%3EWelcome%20to%20your%20Excel%20discussion%20space!%3C%2FA%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20Device%20and%26nbsp%3B%3C%2FSPAN%3E%3CU%3EOS%20platform%3C%2FU%3E%3CSPAN%3E%2C%20e.g.%2C%20PC%2FWindows%2010%20or%20Mac%2FmacOS%20Big%20Sur%2011.2.1%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E-%20Excel%20product%20name%20and%26nbsp%3B%3C%2FSPAN%3E%3CU%3Eversion%20number%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(re.%20your%20report%3A%20the%20kind%20of%20setup%20that%20necessarily%20causes%20problems%20when%20calcs%20are%20required)%3C%2FP%3E%3CP%3EA%20Power%20Query%20solution%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513721%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20variable%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BThank%20you%20so%20much!%20That%20worked%20perfectly!%20Could%20help%20with%20another%20formula%20for%20this%20same%20sheet%20-%20instead%20of%20taking%20the%20sum%20of%20columns%2C%20I%20would%20like%20to%20calculate%20the%20difference%20between%20the%20value%20in%20Column%20C%20and%20Column%20B%20(the%20first%20preceding%20row%20with%20a%20value%20in%20B).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I would like to find a formula that will take the total of a column -- but the number of rows in that column will vary.  The attachment hopefully helps to visualize how the totals should be grouped together.

 

Sum hours 1 (column F):  this is the total of column D, starting from the row that has a value in column B, through the next row with a value in column C.  The next sum starts with the next row that has a value in Column B.  In column F, the sum is populated to the row with a value in column B.

 

Sum hours 2 (column G):  this is the total of column E, starting from the row that has a value in column B, through the next row with a value in column C.   The next sum starts with the next row that has a value in Column C.  In column G, the sum is populated to the row with a value in column C.

5 Replies

@richzip1 

Kind reminder from Welcome to your Excel discussion space!:
- Device and OS platform, e.g., PC/Windows 10 or Mac/macOS Big Sur 11.2.1
- Excel product name and version number

 

(re. your report: the kind of setup that necessarily causes problems when calcs are required)

A Power Query solution attached

best response confirmed by richzip1 (New Contributor)
Solution

@richzip1 

A formula solution that assumes data are exactly as in your sample

 

Demo.png

in F2 and copy right then down:

=IF(
    AND(ISNUMBER($B2),ISNUMBER($C2)), D2,
    IF(ISNUMBER($B2), SUM(OFFSET($D2,,COLUMNS($F:F)-1,MATCH(INT($B2), INT($C2:$C15),0))), "")
)

and validate it with CTRL+SHIFT+ENTER (or its equiv on Mac) if you don't run Excel 365/Web

Corresponding sample attached

@L z.   Thank you so much! That worked perfectly! Could help with another formula for this same sheet - instead of taking the sum of columns, I would like to calculate the difference between the value in Column C and Column B (the first preceding row with a value in B).

 

 

@L z.    

Actually I tried the formula above for a larger scale worksheet, and it's not working quite right. In the attached example, F2 should be the sum of D2-D5. However, it's summing all the way down to row 110. If I'm looking at the formula correctly, it's looking for the first row in column C that has the same date as B2.

 

However, it should only take the sum through the next row where column C has ANY value -- the date does not have to match what's being looked at in column B.

 

@richzip1 

In that case, in F2 and copy in G2 and down:

=IF(
    AND(ISNUMBER($B2),ISNUMBER($C2)), D2,
    IF(ISNUMBER($B2), SUM(OFFSET($D2,,COLUMNS($F:F)-1,MATCH(TRUE,ISNUMBER($C2:$C347),0))), "")
)

For the Elapsed Time in H2 and copy down:

=IF(
    AND(ISNUMBER(B2),ISNUMBER(C2)), C2-B2,
    IF(ISNUMBER(B2), INDEX(C2:C347,MATCH(TRUE,ISNUMBER(C2:C347),0))-B2, "")
)

Both formula to be validated with Ctrl+Shift+Enter if you don't run Excel 365