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

  • 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)
    )
    
    

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)
    )
    
    
  • JWood
    JWood ✭✭

    Thank you that worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!