Sheet Summary formula IF AND
Hi, I'm looking for a formula for my Sheet Summary that will count the instances of checks in a checkbox column (CD), but only if the Status column does not show Complete.
I've made a start with this, but it's #unparseable.
=COUNTIF(AND(CD:CD, "1", [Status, "Complete")
Many thanks!
Best Answers
-
Use Countifs when you have 2 criteria:
(and you don't need the quotes around the 1)
=COUNTIFS(CD:CD,1,Status:Status,"Complete")
-
Hello @LisaB:-) ,
To exclude specific items when counting in a list, we will want to use the <> operator. This operator is used for "Does not Equal" instances.
The Formula may look something like this: =COUNTIFS(CB:CB, 1, Status:Status, <>"Complete")
Furthermore, please see my example sheet below demoing this formula:
Please see this link for list of operators: https://help.smartsheet.com/learning-track/get-started/formula-basics
Let me know if you have any questions!
Regards
Sean
Answers
-
Use Countifs when you have 2 criteria:
(and you don't need the quotes around the 1)
=COUNTIFS(CD:CD,1,Status:Status,"Complete")
-
That's perfect, thank you!
How could I then exclude 'Completes' from the formula? So the Status could be: 'Not Started', or 'In Progress', or 'On Hold', but NOT 'Complete'?
-
Just bumping this up.
-
Hello @LisaB:-) ,
To exclude specific items when counting in a list, we will want to use the <> operator. This operator is used for "Does not Equal" instances.
The Formula may look something like this: =COUNTIFS(CB:CB, 1, Status:Status, <>"Complete")
Furthermore, please see my example sheet below demoing this formula:
Please see this link for list of operators: https://help.smartsheet.com/learning-track/get-started/formula-basics
Let me know if you have any questions!
Regards
Sean
-
This is really good, thank you Sean. I didn't know about <> before, and will use it much more in the future.
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!