COUNTIFS with multiple IS NOT statements

ALMF · 01/12/22

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!

Paul Newcome · 01/12/22

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?

Paul Newcome · 01/12/22

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

Paul Newcome · 01/12/22

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?

ALMF · 01/12/22

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!

Paul Newcome · 01/12/22

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

ALMF · 01/12/22

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?

Paul Newcome · 01/13/22

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

ALMF · 01/19/22

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

ALMF · 01/23/22

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

Paul Newcome · 01/24/22

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.

ALMF · 01/29/22

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!

Andrée Starå · 01/31/22

Hi @ALMF

I hope you're well and safe!

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

More information.

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

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!

ALMF · 02/01/22

Thank you, Andrée!

Andrée Starå · 02/01/22

@ALMF

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!

Michael Chohrach · 08/02/22

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

Genevieve P. · 08/02/22

Hi @Michael Chohrach

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

Then add that to

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)

or in your case:

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

Cheers,

Genevieve

K Goforth · 05/19/23

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!

Formulas and Functions

Formulas and Functions

COUNTIFS with multiple IS NOT statements

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary