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

RobWL ✭✭
edited 05/17/22 in Formulas and Functions

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?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!