COUNTIFS, OR with three reference columns
I'm trying to test for three conditions with a COUNTIFS. One column consists of status checking for "70", the next two columns are dates that must be populated or other functions will not produce correct data.
Count If, Status =70, Actual Start = Blank or Actual End = Blank
I've tried:
=COUNTIFS({2020 Work Plan- Status}, "70", OR({2020 Work Plan - Actual Start} = "", {2020 Work Plan - Actual End} = ""))
=COUNTIFS({2020 Work Plan- Status}, "70", OR({2020 Work Plan - Actual Start}, ISBLANK(@cell), {2020 Work Plan - Actual End}, ISBLANK(@cell) = ""))
=COUNTIFS({2020 Work Plan- Status}, "70", OR({2020 Work Plan - Actual Start} = ISBLANK(@cell), {2020 Work Plan - Actual End} = ISBLANK(@cell) = ""))
Is it even possible?
Best Answer
-
I believe that Countifs is only AND based
2 workarounds
add a column on each line to check for whether either start or end is blank and use that in your formula
(count where 70 and start is blank) + (count where 70 and end is blank) - (count where 70 and both start and end are blank)
Answers
-
I believe that Countifs is only AND based
2 workarounds
add a column on each line to check for whether either start or end is blank and use that in your formula
(count where 70 and start is blank) + (count where 70 and end is blank) - (count where 70 and both start and end are blank)
-
Thank Ron,
I am able to test for Status 70 and one of the other columns, looks like I'll be displaying two results. Number missing Actual Start as well as number missing Actual End, even though it may be an incident of both being missing from the same line.
Appreciate your assistance,
Mark
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!