Multiple COUNTIFS plus OR

Amber Jackson

Hi gurus! Here is our question of the week:

I have a formula with multiple COUNTIFS, but I also need an OR...with a COUNTIF and CONTAINS. But this doesn't seem to be working in my favor! (Getting the Unparseable error.) Here's what I have so far:

=(COUNTIFS({Sheet1}, "2020", {Sheet1}, "3") + COUNTIFS({Sheet2}, "2020", {Sheet2}, "3") ...COUNTIFS({Sheet5}, "2020", {Sheet5}, "3")), OR(COUNTIF({Sheet1}, CONTAINS("20I", @cell))))

Any tips?

David Tutwiler

I think your issue is the ordering of the formula. I can't quite tell what you're trying to get the OR to do here, but the OR should be at the point in a formula where you need to make a distinction between two different events. Where you have it, at the end, there is nothing for it to resolve to.

Can you describe what you're trying to get the OR to do in this formula and then that might lead to a solution. Please use the @mention so that I will get a notification when you post your reply.

Amber Jackson

Hi @David Tutwiler

I think you are correct, but I wasn't able to get that to work either.

I need to count the cells that fall into the large COUNTIFS category. If a cell doesn't fall into that category, I need to look in the "20I" criteria to count it and include in the overall calculation.

I hope this makes sense!

David Tutwiler

That does make sense. I think you'll need to include the IF in each of the COUNTIF statements then. So it would read "If the counts in the large category contain a result, then use that number, otherwise, look at a different range. Something like this should work:

=IF(COUNTIFS({Sheet1}, "2020", {Sheet1}, "3") > 0, COUNTIFS({Sheet1}, "2020", {Sheet1}, "3"), COUNTIF({Sheet1}, CONTAINS("20I", @cell)))

Then you would just repeat that with a + to add your other calculations for your other sheets.

Amber Jackson

Wow--that is going to be a huge equation! Is there no easier/cleaner way to accomplish this?

David Tutwiler

Not that I can think of. This is the only way I know how to check for 1 condition, and if it isn't met run a different calculation.

You could use helper columns to break up your formula into smaller chunks if the size of the formula is a concern. You'd essentially create a column for each sheet formula and then your main column would just add all the helper columns together.

So I think it is a question of more columns or a long formula.

Amber Jackson

Thank you David!