AVERAGEIF when referencing another sheet
I've used this formula multiple times and I am not sure what I am doing wrong on this sheet but I cannot get it to calculate. I have tried, =AVERAGEIF({CSB QA Report Range 1}, Name@row, {CSB QA Report Range 2}), I have tried =AVERAGEIF({CSB QA Report Range 1}, CONTAINS(Name@row, @cell), {CSB QA Report Range 2}), and also =AVG(COLLECT({CSB QA Report Range 2}, {CSB QA Report Range 1}, CONTAINS(Name@row, @cell), 0)). I keep getting #INCORRECT ARGUMENT SET. I really which there was a way to step into these formulas to see where the error is occurring.
The first range is the range that has the conditional date like employee names on the sheet that I am referencing, and the second range is what I want averaged based on the employee name in that row.
First Range
Second Range
Answers
-
Do you have any names in the Contact Center Employees column? I see you have blocked out some data in the two columns to the left of it, but I don't see anything in the column being referenced.
-
I do have name there, I just stumbled across something. If I change the range to only include one name column row then it works but if I want it to look for specific names in multiple columns that's when the error occurs. The range sheet has multiple locations where employees take calls and it is broken down by location which is why there is more than one column for employee names.
-
Yes. All ranges within a single function must be of the same size and shape. In this case both must be single columns OR both must be multiple columns.
My suggestion would be to add a helper column on the source sheet that joins all of the name columns on each row into a single string within the cell then searching this string for the name (using the CONTAINS function).
-
suggestions on a helper column formula that copies the names if one exists in the other rows?
-
Try something like this (update column names appropriately):
=JOIN(COLLECT([1st Employee Column]@row:[Last Employee Column]@row, [1st Employee Column]@row:[Last Employee Column]@row, @cell <> ""), ",")
-
Perfect, thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!