COUNTIFS Error

Julie Nelson

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

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

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.

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.

Cheers,

Genevieve

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

@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!