Is there a way to count a certain column based on the day of the week?

Mark.Hendley

Is there a way to count a certain column based on the day of the week?

what I want

count the "Hold" (Yellow Box) in the Monday column if today is Monday or count the "Hold" (Yellow Box) in the Tuesday column if today is Tuesday..... continued for each day

This is my current formula

=COUNTIFS({Daily check Monday}, "hold" + COUNTIFS({Daily check Tuesday}, "hold" + COUNTIFS({Daily check Wednesday}, "hold" + COUNTIFS({Daily check Thursday}, "hold" + COUNTIFS({Daily check Friday}, "hold")))))

There are 26 checks that need to be done daily, the current formula would work in an ideal situation. Ideal situation being that all of the checks get done every day.

Right now with this formula it combines all the checks that need to be completed including ones that weren't done the day before, I just want to know how many are needed today.

Thanks!

Nick Korna

Hi @Mark.Hendley,

You can get the number of missed checks by using a range rather than needing to add the COUNTIFS together. For example, this would show the whole week (possibly for use as a sheet summary type metric for the end of the week if your sheet is weekly and a new one is produced from a template):

=COUNTIF(Monday:Friday, "hold")

For the current day, you can use a nested IF statement, along the lines of:

=IF(WEEKDAY(TODAY()) = 2, COUNTIF(Monday:Monday, "hold"), IF(WEEKDAY(TODAY()) = 3, COUNTIF(Tuesday:Tuesday, "hold"), IF(WEEKDAY(TODAY()) = 4, COUNTIF(Wednesday:Wednesday, "hold"), IF(WEEKDAY(TODAY()) = 5, COUNTIF(Thursday:Thursday, "hold"), IF(WEEKDAY(TODAY()) = 6, COUNTIF(Friday:Friday, "hold"), "")))))

This would take the current day's "hold" values. If you want to include previous days, you would modify the range being checked to have the first part of the range. For example, if you wanted to see the missed checks for both Monday & Tuesday on Tuesday you would alter the portion of the formula for Tuesday (WEEKDAY = 3) like so:

IF(WEEKDAY(TODAY()) = 3, COUNTIF(Monday:Tuesday, "hold")

Hope this makes sense, but if I've misunderstood anything or you have any problems/questions then just post! 🙂

Nick Korna

Hi @Mark.Hendley,

You can get the number of missed checks by using a range rather than needing to add the COUNTIFS together. For example, this would show the whole week (possibly for use as a sheet summary type metric for the end of the week if your sheet is weekly and a new one is produced from a template):

=COUNTIF(Monday:Friday, "hold")

For the current day, you can use a nested IF statement, along the lines of:

=IF(WEEKDAY(TODAY()) = 2, COUNTIF(Monday:Monday, "hold"), IF(WEEKDAY(TODAY()) = 3, COUNTIF(Tuesday:Tuesday, "hold"), IF(WEEKDAY(TODAY()) = 4, COUNTIF(Wednesday:Wednesday, "hold"), IF(WEEKDAY(TODAY()) = 5, COUNTIF(Thursday:Thursday, "hold"), IF(WEEKDAY(TODAY()) = 6, COUNTIF(Friday:Friday, "hold"), "")))))

This would take the current day's "hold" values. If you want to include previous days, you would modify the range being checked to have the first part of the range. For example, if you wanted to see the missed checks for both Monday & Tuesday on Tuesday you would alter the portion of the formula for Tuesday (WEEKDAY = 3) like so:

IF(WEEKDAY(TODAY()) = 3, COUNTIF(Monday:Tuesday, "hold")

Hope this makes sense, but if I've misunderstood anything or you have any problems/questions then just post! 🙂

Mark.Hendley

@Nick Korna You my friend, are the best! This worked perfectly, Thank you.

Nick Korna

No problem - I've just done a quick edit to fix a slight issue with the day numbers on Thursday/Friday.

Mark.Hendley

Yeah I just noticed that too, Thanks again!