Is there a way to count a certain column based on the day of the week?
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!
Best Answer
-
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! 🙂
Answers
-
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 You my friend, are the best! This worked perfectly, Thank you.
-
No problem - I've just done a quick edit to fix a slight issue with the day numbers on Thursday/Friday.
-
Yeah I just noticed that too, Thanks again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!