Counting the number of occurrences before the current week #

@ TMS

In response to using CountIFS I have also tried-

=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.

Any help is appreciated, I know it has to be an easy solution but my brain just is not seeing it.

Thanks!

Oh...right.

Okay, attached is the stats table. The row labeled "total late" is the one giving me problems.
I have a test entry put into the log showing a late job from three weeks ago.

I can get the 'current week' to read correctly simply by doing a count of the 'on-time' column on the attached stats log.
However, I cannot get the past weeks to pick up the late entry. The 3 weeks column is blank because the last formula I tried would not even let me leave it in the cell.

That was =COUNTIFS(Table2[On-time],"No",Table2[Due Week # Hidden],WEEKNUM(NOW())-3)
I want the formula to return the # of times "No" shows up in the 'on-time' column of the stats log based on which week the
job is due (Due Week # Hidden).

I figured it would be easiest to try =Countifs(Table2[On-time],"No",Table2[Due Week # Hidden], <Weeknum(Now())) to get it to simply count how many "no"s are in the 'on-time' column for week #'s less than the current week #. That way, since a new week is added to the table each week, I am not always having to update with a new Weeknum(now())-1,-2, etc. But I have had no luck with that idea.

Thanks,

Stats Log.gif
Stats Table.gif

Any Ideas?

Here is the sample workbook.

The blank cells in the Total Late row are the ones I am attempting to get the formula to fill.

I've not looked at your sample workbook, but your formula needs to be amended slightly, otherwise the WEEKNUM function is just taken as a string. Try this (changes shown in red):

=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<"&WEEKNUM(NOW()))

Hope this helps.

Pete

That did it, thank you Pete_UK!

Glad to hear it - thanks for feeding back.

Pete