Count a Range of Dates IF the box is checked

Emily Rodgers

There are columns to the right (columns names 1 to 32) that have dates in them that increase incrementally.

Here's my sheet.... the column "Number of Appt btwn 3/1 and 12/31/20" has the following formula: =COUNTIFS([1]@row:[32]@row, IFERROR((@cell), 0) >= DATE(2020, 3, 1), [1]@row:[32]@row, IFERROR((@cell), 0) <= DATE(2020, 12, 31)) I want to count the number of dates that fall between 3/1 and 12/31...this formula is currently working.

What I want is to populate the "March to December at Risk" column with the same information ONLY IF the "COVID-19 Affected?" column is checked. I can change the value of that column if needed to a symbol or even True/False.

So basically, "Number of Appt btwn 3/1 and 12/31/20" tells me how many visits there would have been if COVID-19 never happened. "March to December at Risk" tells me, based on whether or not the patient has been affected by COVID-19, how many visits we will support. I tried doing this with an IF statement and then didnt work, I also tried adding it as another condition on the COUNTIF and that didnt work. Any ideas?

Debbie Sawyer

Try:

=IF([COVID-19 Affected?]@row=1,COUNTIFS([1]@row:[32]@row, IFERROR((@cell), 0) >= DATE(2020, 3, 1), [1]@row:[32]@row, IFERROR((@cell), 0) <= DATE(2020, 12, 31)))

Does this work?

Kind regards

Debbie