# Help on Countif formula

Options
✭✭✭✭
edited 12/09/19

Hi I am trying to create a countif formula with multiple criteria . Heres what I am trying to do

Countif Column1 = 1 and column 2 = N/A or Received at home

I tried this formula but assume it is a check And for all 3 conditions as I get a value of 0

=COUNTIFS([Campus Id #]:[Campus Id #], =1, [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], "N/A", [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], ="RECEIVED AT HOME")

I also tried this but does not work either

=COUNTIF([Campus Id #]:[Campus Id #], =1)+COUNTIF([ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], "N/A") + COUNTIF([ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], ="RECEIVED AT HOME")

Any help is appreciated

Tags:

• ✭✭✭✭✭✭
Options

=COUNTIFS([Campus Id #]:[Campus Id #], 1, [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], "N/A", [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], "RECEIVED AT HOME")

Did that work? Did you get a specific error? This chart of Smartsheet errors can sometimes help troubleshoot what the issue is:

https://help.smartsheet.com/articles/2476176-formula-error-messages

• ✭✭✭✭
Options

I will try it - thank you

I did get around it by using

=COUNTIFS([Campus Id #]:[Campus Id #], =1, [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], <>"Allergy to ASA", [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], <>"MEDICAL REASON", [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], <>"OTHER")

Not sure why this worked but...

• ✭✭✭✭✭✭
Options

Interesting. I am not sure what was causing the issue either. Do you know what error message you were receiving before?

• ✭✭✭✭
Options

I did not get an error message it was that the counted values were not correct

• ✭✭✭✭
Options

I tried this formula but assume it is a check And for all 3 conditions as I get a value of 0

=COUNTIFS([Campus Id #]:[Campus Id #], =1, [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], "N/A", [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], ="RECEIVED AT HOME")

I also tried this but does not work either the value I got was incorrect

=COUNTIF([Campus Id #]:[Campus Id #], =1)+COUNTIF([ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], "N/A") + COUNTIF([ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], ="RECEIVED AT HOME")

• ✭✭✭✭✭✭
Options

Gotcha. Probably because it was counting items that it shouldn't have. Glad you got it working!

• ✭✭✭✭✭✭
Options

This would also work:

=COUNTIFS([Campus ID #]:[Campus ID #], 1, [ASA Not Given in ERSquad]:[ASA Not Given in ERSquad], OR(@cell = "N/A", @cell = "Received at Home"))

• ✭✭✭✭✭✭
Options

The reason the original formula is not working is because the COUNTIFS will only count those cells that match ALL criteria. Your original formula is essentially saying to count those cells that are both "N/A" AND "Received at Home". Unless you have a cell that can say only one and both all at the same time, the result will always be 0.

I suggest nlarsen's version as that specifies exactly what criteria to look for as opposed to what not to count. Specifying what not to count can cause an issue if you happen to have a blank cell or an invalid entry.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!