COUNTIFS across multiple columns

01/12/22
Accepted

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 Answer

  • Paul NewcomePaul 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?

    thinkspi.com

Answers

  • Paul NewcomePaul 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?

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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



    thinkspi.com

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

  • 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 NewcomePaul 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.

    thinkspi.com

  • 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.