Finding the average of multiple rows given 3 criteria (Two Sheets)

RobWL

Context- I am attempting to track the average settlement percentage for a given attorney at an opposing firm with a given client of theirs. In the first screenshot, I've listed that given attorney and their client in columns 1 & 2 (Sheet 1). We are using data from one of our attorney's tracking sheet (Sheet 2). It lists all information for that given case.

There are other columns I need to calculate for, but I can't get even the first formula to work. I've tried AVERAGEIF as well as AVG(COLLECT but to no avail.

I believe the issue I am running into is that I am needing the formula to recognize part of a value listed in the Case Info column, since no two cases will have the same name and I'd rather not include another column.

Here is my formula and I've included the screenshots below:

=AVG(COLLECT({%s}:{%s}, {OC Range}:{OC Range}, ="Attorney1", {Style Range}:{Style Range}, IF(CONTAINS("Capital One", {Style Range}:{Style Range}), "Capital One")))

To sum it up, I am trying to find the average % for every row where OC = Attorney 1 and Case Info contains a given Creditor ("Capital One" for example) and is listed as a Settlement in the Type column and have that listed in sheet 1. (I did not include the type part in my initial formula because I was just trying to get the first part to work)

Am I using the incorrect formulas, is this not possible or am I just missing something entirely?

Paul Newcome

That means you don't have any rows in your source data that fit the criteria. I see in your formula you are specifying "Attorney1", but it looks like your source data in the screenshots has a space before the number. Try adjusting that and see if it works.

Paul Newcome

Try this:

=AVG(COLLECT({%s}, {OC Range}, @cell = "Attorney1", {Style Range}, CONTAINS("Capital One", @cell)))

RobWL

This one popped up as Divide by zero- but we're getting somewhere!

Paul Newcome

That means you don't have any rows in your source data that fit the criteria. I see in your formula you are specifying "Attorney1", but it looks like your source data in the screenshots has a space before the number. Try adjusting that and see if it works.

RobWL

Bingo- thanks a bunch Paul!

Paul Newcome

Happy to help. 👍️