COUNTIFS with multiple IS NOT statements

ALMF
ALMF ✭✭
edited 12/08/23 in Formulas and Functions

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!

Best Answers

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    ALMF ✭✭

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    ALMF ✭✭

    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
    Paul Newcome ✭✭✭✭✭✭

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



  • ALMF
    ALMF ✭✭

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

  • ALMF
    ALMF ✭✭

    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
    Paul Newcome ✭✭✭✭✭✭

    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
    ALMF ✭✭

    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å
    Andrée Starå ✭✭✭✭✭✭

    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.

    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.

  • ALMF
    ALMF ✭✭

    Thank you, Andrée!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

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

    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.

  • Michael Chohrach
    Michael Chohrach ✭✭✭✭✭

    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.
    Genevieve P. Employee Admin

    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

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