Help on Countif formula

bday2329

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

Mike Wilday

I think you can remove the = sign in front of your formulas. 

=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

bday2329

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...

 

Mike Wilday

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

bday2329

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

bday2329

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")

 

Mike Wilday

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

Nic Larsen

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"))

Paul Newcome

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.