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. 😍

Paul Newcome

The problem is that your ranges do not match. My suggestion would be a helper column on the source sheet that counts up every "None" on a row by row basis. Then in your metrics sheet you can use a SUMIFS to add up the numbers in the helper column based on the ID.

Paul Newcome

Are you able to provide some screenshots for context?

bourgeoj

Sure thing and thank you for replying.

The "G-List" source sheet I'm referencing. You see my {G-List | Dependencies} is a range over several columns (side-by-side). There are 1282 cells in the range that = "None", but knowing that doesn't help me.

My "Metrics" sheet where I am writing the formula. I added a view of some of my attempts to get this. The multi-column range I want to reference is {G-List | Dependencies}

Paul Newcome

The problem is that your ranges do not match. My suggestion would be a helper column on the source sheet that counts up every "None" on a row by row basis. Then in your metrics sheet you can use a SUMIFS to add up the numbers in the helper column based on the ID.

bourgeoj

Thanks for your response, Paul. I saw that answer for another posting and was hoping it didn't apply to my issue. Counting "None" is just a starting point though and I don't want to add a helper column for each drop down option.

Can you tell me what you mean by my ranges do not match? Is it because I'm using INDEX? COUNTIFS should work with different ranges, but it doesn't here. Separately the formulas work, is there no way to combine them?

COUNTIFS({G-List | Dependencies}, @cell = "None") result=1282

COUNTIFS({G-List | Ops ID No.}, @cell = [Ops ID No.]@row) result=1

Paul Newcome

I meant your ranges do not match in size. Ranges within the same function must be the same shape and size. They all need to be a single column or the same number of columns if multiple. If you select a 3x5 set of cells, then all ranges within the same function need to be 3x5 (not even 5x3).

bourgeoj

Well that is unfortunate. Thanks for clarifying. At least I can stop the insanity and settle on a different approach. I appreciate your time, Paul.🌼

Paul Newcome

Happy to help. 👍️