COUNTIFS with an AND

Nic Sanchez

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)

KPH

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)

KPH

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)

Nic Sanchez

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

KPH

Thanks for letting me know.