Formula Help Please

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

Trying to get a value for columns with Yes.  Not total but countif(or(col 1 "yes, col 2 yes etc.

I've tried every variation of this: =IF(OR(COUNTIF([O2 under 5 lpm]1, "yes", ([O2 over 5 lpm]1, "yes"))




  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭


    =Countif([O2 under 5 lpm]1, "yes") + Countif([O2 over 5 lpm]1, "yes")

    That will count all yesses in the first column and then add the count of all yesses in the 2nd column. 

  • tcanelli41471
    tcanelli41471 ✭✭✭✭

    but i don't want a total for both columns, just a single value if either column has yes.  the OR statements are not working.  and i even copied a working if(or from another sheet.  

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Still not 100% clear on what you're after, so I've given 2 options.

    This will give you a total count for both columns if either contains a "Yes":

    =COUNTIF([O2 under 5 lpm]:[O2 over 5 lpm], "Yes") - COUNTIFS([O2 under 5 lpm]:[O2 under 5 lpm], "Yes", [O2 over 5 lpm]:[O2 over 5 lpm], "Yes")

    This will give you a value of 1 for each row (i.e. in a cell either side of your  values) where a "Yes" occurs:

    =IF(OR([O2 under 5 lpm]1 = "Yes", [O2 over 5 lpm]1 = "Yes"), 1, "")

    Kind regards,

    Chris McKay

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Can you post a screenshot showing what data you are working with so we can better understand what you are trying to do?

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Yep, I'm with Mike here. Still not clear on what you're after. A screenshot would be ideal.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!