Avg. Amount Based on Several Criteria

Nrob14

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

Paul Newcome

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

Shimanta Roy

Hello Nick,

Try the following:

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

Nrob14

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

Paul Newcome

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

Nrob14

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

Thanks for your help!

Paul Newcome

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

Nrob14

That worked! Thank you for your help!

Paul Newcome

Happy to help. 👍️