I'm receiving a #NO MATCH error with a simple COUNTIFS formula. I'm trying to get a count of the number of times "Routine" occurs in the column, "Workover Type (Routine, Non-Routine)" (text/number column, with a formula), when the "Status" is "SI - Unassigned to WO Rig" (dropdown list column type). Any help is appreciated!



  • Razetto
    Razetto ✭✭✭✭✭

    @Julie Nelson Hi, Try using the @cell in your formula. =COUNTIF(Status:Status, @cell = "SI- Un...", [workover...]:[workover...], @cell= "Routine")

  • Julie Nelson
    Julie Nelson ✭✭✭✭

    @Razetto I added @cell, but it still results in the #NO MATCH error:

    =COUNTIFS(Status:Status, @cell = "SI - Unassigned to WO Rig", [Workover Type (Routine, Non-Routine)]:[Workover Type (Routine, Non-Routine)], @cell = "Routine")

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Julie Nelson

    COUNTIFS won't return a NO MATCH error, since it's not matching anything. To me, this indicates that one of the columns you're referencing has an error!

    Formula errors will bubble up through any other formula referencing it... this means that if even just one cell has a formula error and you reference the entire column (including that one cell), the current formula will produce the same error.

    Try wrapping an IFERROR around all your other formulas:

    =IFERROR(formula, "")

    This should then resolve your COUNTIFs formula looking at those other columns.



  • Razetto
    Razetto ✭✭✭✭✭

    @Julie Nelson What about using contains? =COUNTIFS(Status:Status, CONTAINS("SI - Unassigned to WO Rig", @cell), [Workover Type (Routine, Non-Routine)]:[Workover Type (Routine, Non-Routine)], CONTAINS("Routine",@cell))

  • Julie Nelson
    Julie Nelson ✭✭✭✭

    @Genevieve P. You were right, there was an error in one of the columns that was causing the #NO MATCH! I forgot to check for that.

    @Razetto Your answer was correct the first time! The error was throwing me off.

    Thank you both for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!