Average Formula with an OR Criteria

[coldwolf2000]

I have a question about an average formula.

Below is the forumla I am using:
=IFERROR(AVERAGEIFS('MONTH TO DATE RAW DATA'!E:E,'MONTH TO DATE RAW DATA'!C:C,A30,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO"),"0")

I am having it look at raw data on sheet 'MONTH TO DATE RAW DATA' and if comes across any that equals what is in cell A30 then average. I would like to do a OR statement where either match A30 or A31. How would I accomplish that.

 

[Andrew Poulsom]

Welcome to MrExcel.

You would need an array formula:

http://www.mrexcel.com/forum/showthread.php?t=357200

 

[AlphaFrog]

Maybe try something like this...

=(SUMIFS('MONTH TO DATE RAW DATA'!E:E,'MONTH TO DATE RAW DATA'!C:C,A30,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO")+
SUMIFS('MONTH TO DATE RAW DATA'!E:E,'MONTH TO DATE RAW DATA'!C:C,A31,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO"))/
(COUNTIFS('MONTH TO DATE RAW DATA'!C:C,A30,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO")+
COUNTIFS('MONTH TO DATE RAW DATA'!C:C,A31,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO"))

=(SUMIFS(A30))+SUMIFS(A31)) / (COUNTIFS(A30)+COUNTIFS(A31))

 

[coldwolf2000]

Maybe try something like this...

=(SUMIFS('MONTH TO DATE RAW DATA'!E:E,'MONTH TO DATE RAW DATA'!C:C,A30,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO")+
SUMIFS('MONTH TO DATE RAW DATA'!E:E,'MONTH TO DATE RAW DATA'!C:C,A31,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO"))/
(COUNTIFS('MONTH TO DATE RAW DATA'!C:C,A30,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO")+
COUNTIFS('MONTH TO DATE RAW DATA'!C:C,A31,'MONTH TO DATE RAW DATA'!AJ:AJ,"NO"))

=(SUMIFS(A30))+SUMIFS(A31)) / (COUNTIFS(A30)+COUNTIFS(A31))

that seemed to work.

Thanks

 

[Jared_8878]

so with your formula it should looks something like this:
=AVERAGE(IF(('MONTH TO DATE RAW DATA'!C:C={A30,A31})*('MONTH TO DATE RAW DATA'!AJ:AJ="NO"),'MONTH TO DATE RAW DATA'!E:E))