Issue with CountIFS OR

matt_k · 08/17/18

Hi all,

I am running into some trouble iterating on this formula which was a countifs with an "AND." I am now trying to add an "OR" to the first part. I want to see if the Range 1 contains "Zebra" or "Lion" and was entered in the last 90 days. After scanning these communities, I have tried this three different ways and keep getting invalid operation errors.

=COUNTIFS({External Range1}, "Zebra", OR (@cell = "Lion"), {External Range 5}, < TODAY(90))

I then also read a suggestion to create separate statements, but that has also produced the same error:

=COUNTIFS({External Range 1}, "Zebra", {External Range 5}, <TODAY(90)) + =COUNTIFS({External Range 1}, "Lion", {External Range 5}, <TODAY(90))

Thanks in advance for any help.

J. Craig Williams · 08/18/18

1. TODAY(90) is 90 days in the future, not the past. Use TODAY(-90) > DATE

If you need to worry about the future, then add another check for < TODAY()

2. Use OR( criterion1, criterion2)

=COUNTIFS({ref1}, OR(@cell = "Zebra", @cell = "Lion"), {dateref}, >TODAY(-90))

should be what you are looking for.

Craig

matt_k · 08/19/18

Hi Craig,

That worked perfectly. Thanks so much for your help.

Matt

J. Craig Williams · 08/19/18

I am pleased.

Craig

Issue with CountIFS OR

Comments

Categories