CountIFS OR with more than one range

matt_k

Hi,

I am running into some trouble trying to add an OR to a fairly complex CountIFs function. I have tried placing the OR at multiple points, but either get unparseable or invalid operation errors. Here is one attempt:

=COUNTIFS({Range 1}, "Color", {Range 2}, NOT(ISBLANK(@cell)), OR {Range 3}, NOT(ISBLANK(@cell)), {Range 4}, >TODAY(-90))

I am trying to identify the color in Range 1 and then count if the column in Range 2 OR Range 3 is not blank. Finally, I am looking for only those entries in the last 90 days.

Thanks in advance for any help.

 

matt_k

Does anyone have any suggestions on how to approach this?

Mike Wilday

The major issue I see with your formula is that Countifs won't handle OR statements. You can add two countifs together, one that looks at range 2 and then one that looks at range 3. I haven't reviewed carefully the structure of your formula but that is what I see at first glance. 

Try this: =COUNTIFS({Range 1}, "Color", {Range 2}, NOT(ISBLANK(@cell)), {Range 4}, >TODAY(-90)) + COUNTIFS({Range 1}, "Color", {Range 3}, NOT(ISBLANK(@cell)), {Range 4}, >TODAY(-90))

 

sean59916

Yep, was banging my head on this today as well - for some reason logic statements aren't accepted in the criteria! Seems odd?! 

Sean

Mike Wilday

Yep. It doesn't make a lot of sense. But it works. Just add the counts of the various criterion together.