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
-
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
-
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.
-
Thanks for letting me know.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!