Combining countif with weekday function

0
votes

I have a large report that shows scheduled delivery days vs actual delivery days for every store. I need to somehow show how many times we have delivered late over a 6 week period for every store. Is there a way to combine a countif with week day where Thursday is day 1 and Wednesday is Day 7?

For example. Store X is scheduled to receive Deliveries on Friday but over the last 6 weeks we delivered Thursday, Friday, Thursday, Friday, Monday and Tuesday. My formula should return 2. Monday and Tuesday. The two Thursday deliveries are considered early because they Thursday is considered day 1.

2
excel-formula
This is how the data is layed out. – Stefan Quenette

2 Answers

1
votes

Assuming your data is actual text instead of dates; This will return your number of days of late delivery (sample data below):

=SUMPRODUCT((A2:A12=E2)*(MATCH(C2:C12,{"Thursday","Friday","Saturday","Sunday","Monday","Tuesday","Wednesday"},0)>2))

enter image description here

0
votes

You can map Thursday-Wednesday to 1-7 in a column (where A1 is the date):

MOD(WEEKDAY(A1)+2,7)+1

Then count later than Friday (assuming B is the week of the day column from Thursday)

COUNTIF(B:B, "> 2")

If you do not want to create another column, you can use an array formula. A1:A6 is date range, IF returns 1 for late deliveries. Then SUM.

{=SUM(IF(MOD(WEEKDAY(A1:A6)+2,7)+1 > 2, 1, 0))}