Multiple COUNTIFS plus OR
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?
Answers

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.

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!

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.

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

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.

Thank you David!
Help Article Resources
Categories
Check out the Formula Handbook template!