COUNTIFS with multiple value restrictions

SNickNBCUniUSH

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.

Formula.png

.

Any help is greatly appreciated!

Kelly Moore

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

@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!

Formula.png

Kelly Moore

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

@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

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

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])))

Formula.png

Thanks!

Kelly Moore

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