Avg. Amount Based on Several Criteria

Options
✭✭

Hello,

I am trying to write a formula that tells me the average repair amount based on several criteria in different columns on the sheet.

The columns are listed below:

Location Column

Repair Category Column

Repair Amount Column

Basically, if the location is "Location A" and the repair category is "Category 2" then what is the average repair amount in the Repair Amount column.

I've tried several attempts of countifs and contains formulas with no luck. I've used those successfully in the past for basic counts but I've never had to attempt an average as well.

I hope that makes sense! Thank you in advance!

-Nick

• ✭✭✭✭✭✭
Options

You can use a CONTAINS function like so:

=AVG(COLLECT({Repair Amount Range 7}, {Location Range 1}, "AB Calgary", {Repair Category Range 5}, CONTAINS("PDR", @cell)))

• ✭✭✭✭
Options

Hello Nick,

Try the following:

=AVG(COLLECT([Repair Amount]:[Repair Amount], Location:Location, "Location A", [Repair Category]:[Repair Category], "Category 2"))

• ✭✭
Options

Hi Shimanta,

Thanks for the quick reply! I tried that formula and and getting a "#divide by zero" error message.

Here's my formula:

=AVG(COLLECT({Repair Amount Range 7}, {Location Range 1}, "AB Calgary", {Repair Category Range 5}, "PDR"))

The columns I'm referencing are all on a different sheet.

Thanks again,

Nick

• ✭✭✭✭✭✭
Options

That particular error is indicating that you are not pulling in any matching rows. Just for some trouble shooting... What happens when you enter

=COUNTIFS({Location Range 1}, "AB Calgary", {Repair Category Range 5}, "PDR")

• ✭✭
Options

Hi Paul,

I think the issue was that I wasn't typing the exact repair type. So it was not finding a match. I typed the exact repair type and it worked!

However, now my issue is that there are two types of "PDR" repair types that I'm trying to average together. That was why I originally used just "PDR" in my formula hoping it would pick up anything with PDR in the type. That didn't seem to work.

So now I'm wondering how I can average both of the PDR repair types together (PDR Major and PDR Minor).

• ✭✭✭✭✭✭
Options

You can use a CONTAINS function like so:

=AVG(COLLECT({Repair Amount Range 7}, {Location Range 1}, "AB Calgary", {Repair Category Range 5}, CONTAINS("PDR", @cell)))

• ✭✭
Options

That worked! Thank you for your help!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!