# CountIFS OR with more than one range

✭✭
edited 12/09/19

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.

• ✭✭

Does anyone have any suggestions on how to approach this?

• ✭✭✭✭✭✭

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))

• ✭✭✭

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

Sean

• ✭✭✭✭✭✭

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