AVERAGEIFS and range for criteria?

**MaverickBlack** · 02-13-2015, 04:51 AM

Hi,

I have a very nice AVERAGEIFS statement that has saved me a ton of work getting me averages that match 2 separate criteria. However, I have reached a point where I need to add another criteria that is from a range as opposed to a single cell, and I can't figure out how to get it to work. My AVERAGEIFS statement looks something like this:

=AVERAGEIFS(target range, criteria A range, cell A, criteria B range, cell B, criteria C range, cells C1:C# )

This will only return the values from target range that matches A, B and C1, but I need it to include C2, C3, etc.

Looking elsewhere on the forums I came across something that said that AVERAGEIFS cannot hand ORs in the criteria, which leads me to believe that ranges may not work either. If an AVERAGEIFS statement can't do this, what can I use, preferably that doesn't require adding more data to my messy columns?

Thanks

**BadlySpelledBuoy** · 02-13-2015, 05:09 AM

Could you post some sample data in a workbook and show your expected results?

BSB

**XOR LX** · 02-13-2015, 05:35 AM

Hi.

It's probably best to switch to an array formula** in these circumstances, for example:

=AVERAGE(IF(criteria A range=cell A,IF(criteria B range=cell B,IF(ISNUMBER(MATCH(criteria C range,cells C,0)),target range))))

You can also use, if cells C is a range comprising a single row:

=AVERAGE(IF(criteria A range=cell A,IF(criteria B range=cell B,IF(criteria C range=cells C,target range))))

And if it is one comprising a single column:

=AVERAGE(IF(criteria A range=cell A,IF(criteria B range=cell B,IF(criteria C range=TRANSPOSE(cells C),target range))))

Regards

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