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
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!