COUNTIFS with multiple value restrictions

Options

Hello,

I've attached a screenshot. I'm looking to incorporate a restriction on several "Distro" values, but can't seem to crack the code, and not sure if I'm making it more complex than it needs to be. The rest of the formula works fine without the part where I'm trying to restrict.

.

Any help is greatly appreciated!

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @SNickNBCUniUSH

    Try this. Directly into your working CountIfs formula add your restriction. Do not place your number in quotes

    =COUNTIFS(everything you have so far, Distro:Distro, <>4467)

    Does this work for you?

    Kelly

  • SNickNBCUniUSH
    Options

    @Kelly Moore Yes it did! Thanks. I think maybe it didn't want quotes around a number? But now I'm encountering a new problem. The equation has proper syntax, but it's no longer acting as a functioning division formula. When I had just one parameter, it was dividing the total number of "Yes" to the entire number of rows in the column. It's no longer doing that, even though I'm choosing a row that isn't a restricted Distro. Any thoughts? Thank you!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @SNickNBCUniUSH

    To answer the first question - yes it was the quotes around the number. When a number is enclosed by quotes then smartsheet will treat that value as a text string. Smartsheet wasn't finding a textstring with that value since your real data was a number.

    I didn't know you would have multiple numbers to exclude in your countifs formula. Let's see if this helps give you values you are expecting

    =COUNTIFS(everything redacted, Distro:Distro, OR(@cell<>4467, @cell<>5511))/COUNT(redacted)

    If it's still confused, try this

    =(COUNTIFS(everything redacted, Distro:Distro, OR(@cell<>4467, @cell<>5511)))/COUNT(redacted)

    Will this work for you?

  • SNickNBCUniUSH
    Options

    @Kelly Moore - thanks again for your help on this. I got the syntax to work, but the division equation is still not working for some reason. Is there a better formula I should be using than COUNTIFS/COUNT? It's so weird because it worked before I started restricting distros. Thanks!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @SNickNBCUniUSH

    =((COUNTIFS(everything redacted, Distro:Distro, OR(@cell<>4467, @cell<>5511)))/COUNT(redacted))

    try this one. There should be no difference in the division of a COUNTIFS vs COUNT

  • SNickNBCUniUSH
    edited 03/17/23
    Options

    Hey @Kelly Moore Is the attached what you're referring to? I'm still getting syntax error (invalid).

    Let me write it out as well:

    =((COUNTIFS([Redacted]:[Redacted], "Yes", [Redacted:Redacted], "Upper", [Distro]:[Distro], <>"Redacted", Distro:Distro, OR(@cell <> 4467, @cell <> 5511)) / COUNT([Redacted]:[Redacted])))

    =((COUNTIFS([Redacted]:[Redacted], "Yes", [Redacted]:[Redacted], "Upper", Distro:Distro, <>"Redacted", OR([Distro:Distro], <>4467, [Distro:Distro], <>5511))) / COUNT([Redacted]:[Redacted])))

    Thanks!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @SNickNBCUniUSH

    The screenshot above in not using syntax that smartsheet is expecting

    =COUNTIFS(everything redacted, Distro:Distro, <>"redaction, OR(@cell<>4467, @cell<>5511))/COUNT(redacted)

    If there is another criterion that should be included within the OR criteria, it needs to be added inside the OR parenthesis. If there is a criteria that is added as an AND, the easiest is to add another Distro:Distro

    =COUNTIFS(everything redacted, Distro:Distro, OR(@cell<>4467, @cell<>5511, another criteria))/COUNT(redacted)

    or (if there is an AND)

    =COUNTIFS(everything redacted, Distro:Distro, OR(@cell<>4467, @cell<>5511, another criteria), Distro:Distro, another criteria)/COUNT(redacted)

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!