Help with If formula to ignore data based on past criteria.

Hello,

I have a workbook which tracks job status.
The stats worksheet is set up to keep track of a number of metrics, including how many jobs are on-time, how many jobs are late.
I am trying to get the table to where, if the job is set up as a "supplier" in the "customer" column of the status log worksheet then it's late status is ignored when calculating the number of late jobs. The stats page keeps track of the current and previous weeks. For the previous weeks I have it working well. The problem is with the current week.

Right now we have a supplier job which was due last week. It is running late.

This supplier job should show up in the "Supplier Late" row of the stats table (which it does), but it should not be counted in the "Number late" row (which it is). The job became late last wednesday, on thursday and friday (when it was still due during the current week) the formula I had for the 'current week' column 'number late' row worked at keeping the supplier job out of the count.

Now that the due date is in the previous week my formula =COUNTIF(Table2[On-time],"No")-(COUNTIFS(Table2[On-time],"No",Table2[Due Week Hidden],(WEEKNUM(NOW())),Table2[Customer],"Supplier")) no longer subtracts the supplier value from the current week, number late, and I have no idea how to tell it to do that.

I think it is also worth mentioning that the previous week column draws its information from a "change history" worksheet which keeps track of every minute detail of a job. Where as the current week column draws its information from the "status log" worksheet which only contains current details.

I have attached a sample workbook.

Thanks for any and all assistance. Hopefully that explanation made sense.

Sample Workbook.xlsx

Hi
Try this modfied formula:
=COUNTIF(Table2[On-time],"No")-COUNTIF(Table2[Customer],"Supplier")-(COUNTIFS(Table2[On-time],"No",Table2[Due Week Hidden],(WEEKNUM(NOW())),Table2[Customer],"Supplier"))
Hope it helps.
Tony

Thanks for you reply Tony,

I tried out your formula. However, I need it to be able to work if there a multiple Supplier entries in the status log, it is actually sort of unusual that there is only one at the moment.

Hi
The formula will count all of the Supplier entries in the table not just one. Have you added some more suppliers and tested it?
Tony

Yes, I have. The problem is the formula does not count them accurately. So 1 on-time supplier entry skews the measure and results in a negative value in the Number Late field. This negative value grows as the number of on-time supplier entries grows.

Hi
Try this formula. If this doesn't give you what you need then can you provide a more detailed example:

=COUNTIF(Table2[On-time],"No")-COUNTIFS(Table2[Customer],"Supplier",Table2[On-time],"No")-(COUNTIFS(Table2[On-time],"No",Table2[Due Week Hidden],(WEEKNUM(NOW())),Table2[Customer],"Supplier"))

Good luck.
Tony