Count a Range of Dates IF the box is checked
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?
Answers
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!