Cross-sheet referencing to count across multiple columns for a matching row. Can't figure it out :(

bourgeoj ✭✭✭

I created a metrics sheet that references my G-List sheet. In my metrics sheet I'm essentially trying to say, "Look at the matching [Ops ID No.] in the portfolio sheet (G-List) and count the number of cells across multiple columns that have no dependencies indicated (i.e. ="None")."

The common column in both sheets is [Ops ID No.], a 5 digit unique identifier. I usually use INDEX and MATCH to pull/interpret info, but this time I want to reference about 20 different "Dependencies" columns.

Here's my formula attempt which results in #Incorrect Argument Set: =COUNTIFS({G-List | Dependencies}, @cell = "None", MATCH([Ops ID No.]@row, {G-List | Ops ID No.}, 0))

I've tried so many variations and included INDEX, but can't figure it out. Any guidance would be greatly appreciated. 😍


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!