Countifs Function with Multiple Criteria - Best Practices?

ALMF

Hello! I'm having trouble understanding the best way to count multiple criteria in a countifs function. I can write simple countifs formulas, but the more criterion and ranges I add (seeking "and/or" results), the more errors I get. I struggle with combining ranges and criteria, I think because I don't understand how best to write or maintain a proper syntax as the formula becomes more complex.

In the examples attached, I get a nested criteria error or an incorrect result when I try to add two formulas. What am I missing? And how can I get better at this? :)

Kelly Moore

Hey @ALMF

One error you are encountering is not closing one COUNTIFS statement before beginning another. There should be a closing parenthesis for the COUNTIFS before beginning to add the next one.

Whether you want to use OR statements or not within a COUNTIFS depends on the data you are collecting. In the top example, you will collect all of the counts where Location= Store, Color isn't green AND(OR) Stage = Done, Waiting for Materials. The two separate countifs will count a cell twice if there is an option for the row to include both Done And Waiting. However, if this [Production Stage] is a single select dropdown (only one answer is possible at a time) then your choices will always be one OR the other.

=COUNTIFS(LOCATION:LOCATION, "Store", [Production Stage]:[Production Stage], OR(@cell="Done", @cell="Waiting for Materials"), COLOR:COLOR, <>"Green")

I changed your green color to 'does not equal'. If you wanted to use Does Not Contain (I tend to avoid the negative 'Not Contains' just to reduce number of parentheses to keep up with unless I have to use it), then you will also need to include an additional Color:Color entry of Not Empty <>"" criteria so that you don't end up counting blank cells (because blanks also don't contain 'Green').

As you are building formulas, watch the color of the parentheses. These are visually telling you what terms are grouped together. These will help you see if your COUNTIFS are closed or not.

Does this help? If not, ask more questions and the community will try to keep answering until you have a clear understanding.

Kelly

You may also find more help here, if interested

https://help.smartsheet.com/learning-track/best-practice-webinars/formula-foundations?_ga=2.53652780.50812989.1651957499-135334380.1624481222