 SOLVED

# Sum of variable number of rows

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

# Re: Sum of variable number of rows

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

# Re: Sum of variable number of rows

A formula solution that assumes data are exactly as in your sample 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

# Re: Sum of variable number of rows

@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).

# Re: Sum of variable number of rows

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.

# Re: Sum of variable number of rows

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