Excel 2007 : Average if multiple criteria and contains

**AFmonaco** · 02-23-2012, 11:59 PM

I am trying to find the average units sold on one sheet (sht1) of the data on a second sheet(sht2). Does anyone know how i could do this by year="2000" and Car="Contains 2DR"?

I am sort of a novice with excel and do not fully understand the if functions. So any explanation or help would be greatly appreciated.

Sht1:
Year 2000 2Drs Avg Sold:
Year 2000 4Drs Avg Sold:

Sht2:
Car Year Units Sold
Ford Focus 2dr 2000 2
Chrystler 300 4dr 2000 3
Saturn ion 2dr 2000 2
Honda Civic 2dr 2004 1

Thanks!

**DGagnon** · 02-24-2012, 12:19 AM

could you please attach an example set of data in an excel sheet?

**DGagnon** · 02-24-2012, 12:26 AM

i recreated what i think is yoru data set in the attached, and used the AVERAGEIF formula. please take a look at my example to see if this is what you are working with.

the formula used is: =AVERAGEIFS(Sheet2!C:C,Sheet2!A:A,"*"&B2&"*",Sheet2!B:B,A2)

**AFmonaco** · 02-24-2012, 12:37 AM

Thanks, I uploaded my data which is the same as what you created. However, in your attached file I am getting the result: "#value!" Do you think i have a setting wrong on my computer?

**AFmonaco** · 02-24-2012, 12:47 AM

Nevermind. It worked when i selected the cells and not the entire column. The microsoft site said the Range must have the same amount of columns as the criteria. Thanks!

**DGagnon** · 02-24-2012, 12:47 AM

on your version of the file Sheets 1 and 2 were the opposit of mine, i have update the formula for your sheet.

=AVERAGEIFS(Sheet1!C:C,Sheet1!A:A,"*"&B7&"*",Sheet1!B:B,A7)