AVERAGEIF when referencing another sheet

blmccue

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

Paul Newcome

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.

blmccue

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.

Paul Newcome

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

blmccue

suggestions on a helper column formula that copies the names if one exists in the other rows?

Paul Newcome

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 <> ""), ",")

blmccue

Perfect, thank you!

Paul Newcome

Happy to help. 👍️