CountIFs text before today

[flyyboy84]

I do not have permissions to post attachments so I will try to explain as best I can.

I have dates in Row 1 that go to the end of the year (some dates are before today and some are after)

In rows 2-5 I have names workers that randomly work shifts on those dates.

I have a column of all the names listed and would like to count the number of shifts each person has worked UP TO today.

I have a cell with today's date , =Today()

This is what I have come up with, but there is a formatting error.

=COUNTIFS(B2:F5,H2,B1:F1,"<="J2)

B2:F5 is the range of names who are working the shifts under each column date
B1:F1 is the dates in order, some are before today and some are after
H2:H6 are the list of worker names (I would like the total shifts prior to today to be displayed in I2:I6 next to corresponding name
J2 is =Today()

Please help if you can!

 

[Norie]

Try this.

=COUNTIFS(B2:F5,H2,B1:F1,"<=" & J2)

 

[steve the fish]

Maybe sumproduct.

=SUMPRODUCT((B1:F1<=J2)*(B2:F5=H2))

 

[flyyboy84]

Thanks, but that's the exact formula I have (forgot to add that to my original post), but it comes up with a #VALUE ! error (I think due to formatting).

 

[flyyboy84]

Your the man Steve! Thanks, this worked!