Does COUNTIFS work with only 2 conditions?

Avantika Murthi

Ive been trying to add multiple conditions in to calculate some values for my Sheet Summary, but I cant seem to add more than 2 conditions. Each of them work individually & with 2 conditions, but when i add the third condition it always shows '0'. What am i doing wrong?

The formula I'm using is:

=COUNTIFS([Overall Status]:[Overall Status], "Selected", [Overall Status]:[Overall Status], "WIP", [Overall Status]:[Overall Status], "Pending Verification")

Debbie Sawyer

Hello Avantika

My understanding of the COUNTIFS function is that ALL the criteria must meet true to be counted. So the COUNTIFS function can take as many conditions as is required to fulfil the logic of the query, however each condition must be on different columns. So you could say CountIF the Overall Status is "Selected" and the % Complete is 100% and the Status is "Complete" and the Risk Raised is 0 etc but you cannot ask the same column to match against two different values as in the example you have posted.

My recommendation is to set up a helper column with a nested IF function in it to tick a box if the Overall Status is one of the values you have put in your example, then just count the ticks in that helper column.

Helper column called INSTATUS with a checkbox data type and the following formula in it

=IF(OR([Overall Status]@row = "Selected",[Overall Status]@row = "WIP",[Overall Status]@row = "Pending Verification"),1,0)

Then in a different cell simply =COUNTIF(INSTATUS:INSTATUS,1)

Hope this helps.

Kind regards

Debbie

Paul Newcome

You could also work an OR function into your COUNTIFS to avoid having to use the extra column.

=COUNTIFS([Overall Status]:[Overall Status], OR(@cell = "Selected", @cell = "WIP", @cell = "Pending Verification"))

COUNTIFS basically defaults to AND, so you are saying to count those rows where the [Overall Status] is "Selected" AND is "WIP" AND is "Pending Verification". The problem with this is that a cell cannot equal two different things at the same time.

Avantika

Thank you so much, this really helps!

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful". That way others searching for a similar solution can know that one may be found here.