Forum Discussion
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
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
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.
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
