Cross-sheet referencing to count across multiple columns for a matching row. Can't figure it out :(
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
-
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.
Answers
-
Are you able to provide some screenshots for context?
-
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}
-
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.
-
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
-
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).
-
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.🌼
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!