COUNTIFS with date ranges

Anita Eral-Thompson

Hi, I have tried and have not been successful. What is the formula for COUNTIFS (a certain status) with the date ranges of Monday to Friday of that workweek? I need to count:

1 Test Status = 100% or 50%

Within the dates of Mon 2/8/22 through Fri 2/11/22

Kelly Moore

Hey @Anita Eral-Thompson

I am not sure if you're looking for days against a specific week (ie, you have a date column for say Monday of each week) or if you're looking for the count within whatever this week is. I'm giving you an approach for whatever this week is - if you have a specific date column then substitute that [column]@row for TODAY(). This format is looking at data within the same sheet. If you are needing this across sheets then the format changes to use the cross sheet references. I had to assume the Test Status results were numbers formatted as percents, and not text that looked like percents. Screen shots always help the community provide faster, better solutions.

=COUNTIFS([1 Test Status]:[1 Test Status], OR(@cell=1, @cell=0.5), [Date column]:[Date column], AND(WEEKDAY(@cell)>1, WEEKDAY(@cell)<6, WEEKNUMBER(@cell)=WEEKNUMBER(TODAY()))

https://help.smartsheet.com/function/weekday

https://help.smartsheet.com/function/weeknumber

Does this work for you?

Kelly