COUNTIFS with an AND

I am trying to use a COUNTIFS formula with an AND. I need to account for two different conditions to determine the count of another column.

COUNTIFS the Master - Project Status column is not "complete" AND Master - Sales Stage is "Won" should return a count based on Master - Service from a row.

=COUNTIFS({Master - Project Status}, NOT(@cell = "Complete"), AND({Master - Sales Stage}, (@cell = "Won", {Master - Service}, Label@row)


Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Nic Sanchez

    The COUNTIFS will only count if all criteria are met, so you do not need the AND. By including it and not closing the parenthesis smartsheet will be confused!

    If you are going to use the @cell, you need to close the parenthesis after the criteria.

    There should always be an equal number of ( and )

    Instead of

    =COUNTIFS({Master - Project Status}, NOT(@cell = "Complete"), AND({Master - Sales Stage}, (@cell = "Won", {Master - Service}, Label@row)

    Try

    =COUNTIFS({Master - Project Status}, NOT(@cell = "Complete"), {Master - Sales Stage}, (@cell = "Won"),{Master - Service}, Label@row)

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Nic Sanchez

    The COUNTIFS will only count if all criteria are met, so you do not need the AND. By including it and not closing the parenthesis smartsheet will be confused!

    If you are going to use the @cell, you need to close the parenthesis after the criteria.

    There should always be an equal number of ( and )

    Instead of

    =COUNTIFS({Master - Project Status}, NOT(@cell = "Complete"), AND({Master - Sales Stage}, (@cell = "Won", {Master - Service}, Label@row)

    Try

    =COUNTIFS({Master - Project Status}, NOT(@cell = "Complete"), {Master - Sales Stage}, (@cell = "Won"),{Master - Service}, Label@row)

  • Thank you for much. That worked, I thought I had tried that but maybe I was missing the ). Thank you.

  • KPH
    KPH ✭✭✭✭✭✭

    Thanks for letting me know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!