COUNTIFS, OR with three reference columns

maberry34826

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?

ron.judenberg112096

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)