0

I'm trying to calculate the overtime that has accrued up to and including today's date.

Sample Data

  • Row 2 contains a list of dates.
  • Row 3 the time worked for each date.
  • Row 4 the accrued overtime for each date.

example data

The following formula in I6 should return the SUM of accrued overtime on or before TODAY():

=SUM(HLOOKUP(FILTER(C2:GB2, C2:GB2 <= TODAY()), C2:GB4, 3))

In my example the formula in I6 shaded red should return 5 (the SUM of C4:I4) but instead it returns 1.

What am I doing wrong?

2 Answers 2

1

Welcome!

One of the easiest solutions is to put in cell I6 the formula =SUMIF(C2:I2,"<=" &TODAY(),C4:I4) and repeat this for every week

0

Using SUMIF as suggested by @JohnSUN is a good approach and also slightly shorter (4 chars) than the alternative below.

Your original approach would work if you tweak it to FILTER row 4 based on the date in row 2, then SUM the resulting array:

=SUM(FILTER(
   C4:GB4, C2:GB2<=TODAY())) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.