CountIFS OR with more than one range

matt_k
matt_k โœญโœญ
edited 12/09/19 in Smartsheet Basics

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.

ย 

Comments

  • matt_k
    matt_k โœญโœญ

    Does anyone have any suggestions on how to approach this?

  • Mike Wilday
    Mike Wilday Community Champion

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

    COUNTIFS.png

  • Mike Wilday
    Mike Wilday Community Champion

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