Combining COUNTIF AND IF ?

I'm trying to return a value that represents a number from 0-7.
The numbers represent a step number that has been completed based on how many status cells say "Complete".
So if the first step is "Complete", I need a 0 returned. If the first two steps are complete, then I need a 1 returned etc. (I have to use 0 as the first step unfortunately).
My formula works unless no steps are complete in which case it returns a -1. I'd like for the formula to return something like "NS" ("Not started") of no steps are complete.
=SUM(COUNTIF(Status2, "Complete"), COUNTIF(Status6, "Complete"), COUNTIF(Status14, "Complete"), COUNTIF(Status20, "Complete"), COUNTIF(Status22, "Complete"), COUNTIF(Status27, "Complete"), COUNTIF(Status31, "Complete") - 1)
I'm thinking I could incorporate an IF statement somewhere that returns "NS" of no steps are completed.
Best Answer
-
Try this:
=IF( SUM(COUNTIF(Status2, "Complete"), COUNTIF(Status6, "Complete"), COUNTIF(Status14, "Complete"), COUNTIF(Status20, "Complete"), COUNTIF(Status22, "Complete"), COUNTIF(Status27, "Complete"), COUNTIF(Status31, "Complete") - 1) < 0, "Not Started", ο»Ώο»ΏSUM(COUNTIF(Status2, "Complete"), COUNTIF(Status6, "Complete"), COUNTIF(Status14, "Complete"), COUNTIF(Status20, "Complete"), COUNTIF(Status22, "Complete"), COUNTIF(Status27, "Complete"), COUNTIF(Status31, "Complete") - 1) )
Travis C, PMP
Smartsheet Leader with 5+ years of SS experience
Let's connect: LinkedIn - Travis C.
If my answer was sufficient, pleaseupvote and mark my response as answered.
Answers
-
Try this:
=IF( SUM(COUNTIF(Status2, "Complete"), COUNTIF(Status6, "Complete"), COUNTIF(Status14, "Complete"), COUNTIF(Status20, "Complete"), COUNTIF(Status22, "Complete"), COUNTIF(Status27, "Complete"), COUNTIF(Status31, "Complete") - 1) < 0, "Not Started", ο»Ώο»ΏSUM(COUNTIF(Status2, "Complete"), COUNTIF(Status6, "Complete"), COUNTIF(Status14, "Complete"), COUNTIF(Status20, "Complete"), COUNTIF(Status22, "Complete"), COUNTIF(Status27, "Complete"), COUNTIF(Status31, "Complete") - 1) )
Travis C, PMP
Smartsheet Leader with 5+ years of SS experience
Let's connect: LinkedIn - Travis C.
If my answer was sufficient, pleaseupvote and mark my response as answered.
-
Thank you that worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!