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)

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)

maberry34826

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