AVERAGEIFS with multiple criteria

[olga6542]

I need to calculate the average with the following conditions
1. I need to exclude 0 or blank cells when calculating the average
2. I need to calculate average for values that contains Silk (Silk, Silk/Denim,Cotton/Silk)

Can you help?

[Gregb11]

To get the Average for Sales 18 (Column C), you could try:
=AVERAGEIFS(C2:C7,A2:A7,"*Silk*",C2:C7,">"&0)

[azumi]

According to your table, and you have 3 criteria on A11 to A13, and I just assume you need to average column C, put this on C11 and entered as array formula:

=AVERAGE(AVERAGEIFS(C2:C7;A2:A7;A11:A13;C2:C7;"<>"))

or you need regular formula:

=AVERAGE(AVERAGEIF(A2:A7;{"Silk";"Silk/Deim";"Cotton/Silk"};C2:C7))

[olga6542]

I need average for all the years for the Silk and I need to exclude 0. So the range is C2:D7

[azumi]

Hope this works

[Bo_Ry]

Please try

=AVERAGE(IF(ISNUMBER(SEARCH("Silk",A2:A7))*C2:D7,C2:D7))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).