Help on Countif formula

bday2329
bday2329 ✭✭✭✭
edited 12/09/19 in Formulas and Functions

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:
· Share on FacebookShare on Twitter

Comments

  • Mike Wilday
    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

    · Share on FacebookShare on Twitter
  • bday2329
    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...

     

    · Share on FacebookShare on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • bday2329
    bday2329 ✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • bday2329
    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")

     

    · Share on FacebookShare on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • Nic Larsen
    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"))

    · Share on FacebookShare on Twitter
  • Paul Newcome
    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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!