Help on Countif formula
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
Comments
-
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
-
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...
-
Interesting. I am not sure what was causing the issue either. Do you know what error message you were receiving before?
-
I did not get an error message it was that the counted values were not correct
-
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")
-
Gotcha. Probably because it was counting items that it shouldn't have. Glad you got it working!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!