# COUNTIFS with multiple IS NOT statements

Options
✭✭
edited 12/08/23

Hello! I'm trying to write a formula that tells me how many rows meet the criteria in multiple columns. It works with criteria in two columns, but when I try to incorporate a third, I get an incorrect argument set, or an invalid operation. I can't figure out what I'm missing in the formula.

Here's the formula that works:

=COUNTIFS(PLATFORM:PLATFORM, CONTAINS("YOUTUBE", @cell), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("WFD", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("DELIVERED", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("RFD", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("QC", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("N/A", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("KILLED", @cell)))

This formula gives me an accurate count of all our YouTube deliveries that are not in the following production stages: WFD, DELIVERED, RFD, QC, N/A, KILLED.

Here's the formula that doesn't work (adding column TYPE results in an #incorrect argument set. If I put the TYPE range at the start, I get #unparseable.):

=COUNTIFS(PLATFORM:PLATFORM, CONTAINS("YOUTUBE", @cell), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("WFD", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("DELIVERED", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("RFD", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("QC", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("N/A", @cell)), [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("KILLED", @cell)), TYPE:TYPE, NOT(CONTAINS("GFX", @cell))))

The question I'm trying to answer is "how many YouTube deliveries are not in the following production stages: WFD, DELIVERED, RFD, QC, N/A, KILLED, excluding any deliveries where the TYPE is GFX?" In other words, I don't want our GFX projects to be included in the count.

Is COUNTIFS the best function for a query like this? Thank you!

• ✭✭✭✭✭✭
Options

You have one too many closing parenthesis on the end.

Even fixing that though, there should be a way to make this formula more efficient. Are any of the cells referenced multi-select dropdown type columns or do any of them have text additional to what you are searching for/trying to exclude?

• ✭✭✭✭✭✭
Options

Try this...

=COUNTIFS(PLATFORM:PLATFORM, HAS(@cell, "YOUTUBE"), [Production Stage]:[Production Stage], AND(@cell <> "WFD", @cell <> "DELIVERED", @cell <> "RFD", @cell <> "QC", @cell <> "N/A", @cell <> "KILLED"), TYPE:TYPE, @cell <> "GFX")

«1

• ✭✭✭✭✭✭
Options

You have one too many closing parenthesis on the end.

Even fixing that though, there should be a way to make this formula more efficient. Are any of the cells referenced multi-select dropdown type columns or do any of them have text additional to what you are searching for/trying to exclude?

• ✭✭
Options

Thanks, Paul! It's always something tiny! 😖 I removed one parenthesis at the end, and I'm no longer getting an error. I am, however, getting a result of 0, which tells me I still don't have it right. We should have 117 items that meet the PLATFORM and PRODUCTION STAGE criteria but aren't GFX.

In this instance, the PLATFORM column is multi-select, but PRODUCTION STATE and TYPE are single select.

Thanks so much for the help!

• ✭✭✭✭✭✭
Options

Try this...

=COUNTIFS(PLATFORM:PLATFORM, HAS(@cell, "YOUTUBE"), [Production Stage]:[Production Stage], AND(@cell <> "WFD", @cell <> "DELIVERED", @cell <> "RFD", @cell <> "QC", @cell <> "N/A", @cell <> "KILLED"), TYPE:TYPE, @cell <> "GFX")

• ✭✭
Options

It's unparseable. Is this formula saying "Count items where PLATFORM is YouTube and PRODUCTION STAGE is not equal to WFD, DELIVERED, RFD, QC, N/A, and KILLED, and TYPE is not equal to GFX?" If so, then the argument should be right, right?

• ✭✭✭✭✭✭
Options

Can you provide a screenshot of the formula within the sheet similar to the below?

• ✭✭
Options

Apologies, Paul. I'm buried at work. I'll respond with a screenshot ASAP!

• ✭✭
Options

HI Paul! I tried your formula again, and discovered that the error was a column name in your formula that didn't match my column name exactly. To fix it, I changed the column name to all caps so it would match. It's working now, thank you! It's much more elegant than the solution I tried before.

This works!:

=COUNTIFS(PLATFORM:PLATFORM, HAS(@cell, "YOUTUBE"), [PRODUCTION STAGE]:[PRODUCTION STAGE], AND(@cell <> "WFD", @cell <> "DELIVERED", @cell <> "RFD", @cell <> "QC", @cell <> "N/A", @cell <> "KILLED"), TYPE:TYPE, @cell <> "GFX")

Meanwhile, I'm practicing COUNTIF formulas with data in my sheet. Can you tell me why counting with the formula below results in a #NESTED CRITERIA error? I'm trying to count across multiple columns, then add the results of both counts together:

=COUNTIFS(PLATFORM:PLATFORM, "YOUTUBE", NEW:NEW, 1, [PRODUCTION STAGE]:[PRODUCTION STAGE], NOT(CONTAINS("KILLED", @cell) + COUNTIFS(PLATFORM:PLATFORM, "SITE", COPY:COPY, 1, [PROGRAMMING STAGE]:[PROGRAMMING STAGE], NOT(CONTAINS("KILLED", @cell)))))

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

To be able to count across multiple columns, you will need to close out each COUNTIFS separately and then add them together. In your latest formula, you have the second COUNTIFS technically nested inside of the other.

Add in two more closing parenthesis before the + and remove two from the very end of the formula.

• ✭✭
Options

Thank you, Paul. That worked!

I'm learning formulas from the community discussions on Smartsheet. Your solutions are always clear and helpful!

Can you tell me why some formulas use angle brackets { } and some parenthesis ( ) ? Are these interchangeable?

I'm trying to learn the grammar of formulas. Just when I think I've got the pattern, someone posts a formula with all new syntax!

• ✭✭✭✭✭✭
Options

Hi @ALMF

I hope you're well and safe!

When you, for example, reference other sheets (Cross-Sheet Formulas), you'd use Curly Brackets { }.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
Options

Thank you, Andrée!

• ✭✭✭✭✭✭
Options

You're more than welcome!

Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭
Options

I'm trying to create a formula that will count if either the "Pole Change Out" column or the "Misc." column are not blank. I'm thinking I need to use the OR function in combination with COUNTIF (or COUNTIFS) but nothing I do seems to work. Appreciate any suggestions.

=COUNTIFS(OR([Pole Change Out]:[Pole Change Out] <> "", ([Misc.]:[Misc.] <> "")))

• Employee
Options

The COUNTIFS function essentially has a built in AND between each range and criteria combination. What I would do in your instance is combine 3 separate formulas:

• One count if the Pole Change Out cell is blank

• One count if the Misc. cell is blank

Then subtract

• Total count if both are blank (to get rid of duplicates)

=(COUNTIF(this) + COUNTIF(that)) - COUNTIFS(this and that)

=(COUNTIF([Pole Change Out]:[Pole Change Out], <> "") + COUNTIF([Misc.]:[Misc.], <> "")) - COUNTIFS([Pole Change Out]:[Pole Change Out], <> "", [Misc.]:[Misc.], <> "")

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

Hello,

I'm trying to write a formula that will add things from one column to another column. I've tried to write one based on posts above, but keep getting "#unparseable."

I want to add up the "very satisfied" responses from the Faculty Overall Satisfaction columm to the "Very Satisfied" responses in the Admin Overall Satisfaction column. Here's the current formula returning the error message:

=(COUNTIF([Faculty Overall Satisfaction]:[Faculty Overall Satisfaction], “Very Satisfied”) + COUNTIF([Admin Overall Satisfaction]:[Admin Overall Satisfaction], “Very Satisfied”))

Where is the error? Thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!