Forum Discussion

richzip1's avatar
richzip1
Copper Contributor
Jul 03, 2021
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 gr...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jul 03, 2021

    richzip1 

    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

richzip1's avatar
richzip1
Copper Contributor
Jul 03, 2021

Lorenzo    

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.

 

Lorenzo's avatar
Lorenzo
Silver Contributor
Jul 04, 2021

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

Resources