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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…