Combining COUNTIF AND IF ?

JWood
JWood ✭✭✭
in Formulas and Functions

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.

· Share on FacebookShare on Twitter

Best Answer

  • MedaUser
    MedaUser ✭✭✭✭✭
    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.

    · Share on FacebookShare on Twitter

Answers

  • MedaUser
    MedaUser ✭✭✭✭✭
    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.

    · Share on FacebookShare on Twitter
  • JWood
    JWood ✭✭✭

    Thank you that worked!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!