Return list of values index/match

**spivieguy1** · 08-26-2009, 02:00 PM

Sorry if I missed the answer to this with my google searches.

I have a list of data that I want to average the number in column B when there is a match in column A

COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500

What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.

What direction would you point me to accomplish this? Thanks!

**NBVC** · 08-26-2009, 02:06 PM

In XL2007

Try

=AverageIf(A1:A100,"alpha",B1:B100)

adjust ranges to suit.

**spivieguy1** · 08-26-2009, 02:36 PM

Thanks! I don't know how I missed that.

What would you do if you wanted to do the same thing, but after matching more than one criteria?

**NBVC** · 08-26-2009, 02:39 PM

There is an Averageifs() function for multiple criteria...

e.g.

=AVERAGEIFS(B1:B100,A1:A100,"alpha",C1:C100,"John_Doe")

but note that the column to be averaged is up front instead in this function...