Index and match with result average

[CaptainGravyBum]

Hello,
I'm trying to find the correct formula to search for all instances of a name within column A and return the average of all the totals found in column G if that makes sense.
So if bob smith appears in column a five times and column g has values associated to those entries it will return an average of the totals.
At the moment, I have a formula that is looking up and matching the data, but only returning the first value it matches from column g.

=INDEX($G:$G,MATCH("Bob Smith",$A:$A,0))

I know I need an average or count or something else in the formula, but I'm not finding the correct answer.

Any help would be appreciated.

 

[dreid1011]

Well, the index/match is specifically for returning a value associated with the lookup value from one source to another. You need a completely different formula get what you're after.

Perhaps something like this:

=SUMIF($A:$A,"Bob Smith",$G:$G)/COUNTIF($A:$A,"Bob Smith")

 

[Fluff]

Another option

=AVERAGEIFS(G:G,A:A,"bob smith")

 

[CaptainGravyBum]

Well, the index/match is specifically for returning a value associated with the lookup value from one source to another. You need a completely different formula get what you're after.

Perhaps something like this:

=SUMIF($A:$A,"Bob Smith",$G:$G)/COUNTIF($A:$A,"Bob Smith")

Hi @dreid1011,
Thanks for the quick response. I have tried the above formula but it keeps resulting in a #DIV/0! error so I'm not sure what's happening.

 

[dreid1011]

Hi @dreid1011,
Thanks for the quick response. I have tried the above formula but it keeps resulting in a #DIV/0! error so I'm not sure what's happening.

I don't know either, but perhaps give Fluff's suggestion a try.

 

[CaptainGravyBum]

Another option

=AVERAGEIFS(G:G,A:A,"bob smith")

Hi @Fluff,
Wow!, that's so simple and is spot on. Thanks very much.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[CaptainGravyBum]

I don't know either, but perhaps give Fluff's suggestion a try.

Sorry, I was being a pleb and referencing the wrong column. This formula works a treat as well so now I have two options to choose from.
Almost wish I hadn't spent the afternoon trying to google the answer

 

[dreid1011]

Sorry, I was being a pleb and referencing the wrong column. This formula works a treat as well so now I have two options to choose from.
Almost wish I hadn't spent the afternoon trying to google the answer

Hah, well glad you got it sorted.