Finding the average of multiple rows given 3 criteria (Two Sheets)
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

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

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

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

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.

Bingo thanks a bunch Paul!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 217 Industry Talk
 456 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!