IF formula based on specific values in a range

[Mushroomman]

Hi,

I'm trying to average values based on 2 fields. The 1st is straight forward (by date). The second one isn't as straight forward.

I need to average column C if column A contains houses numbers 31,36,37,41,46,48 and the date is 01-06-16.

So the average of the example below is 9 (houses 31 & 37 are on 01-06-16).

I've used averageifs formula and used the IF with AND before, but only when numbers are in order (between 1 & 10), not when I need to search specific numbers.

Thanks,
Paul

[TABLE="width: 500"]
<tbody>[TR]
[TD]House Number[/TD]
[TD]Date[/TD]
[TD]Yield[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]01-06-16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]02-06-16[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]01-06-16[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03-06-16[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01-06-16[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF($B$2:$B$6="01-06-16"+0,IF(ISNUMBER(MATCH($A$2:$A$6,{31,36,37,41,46,48},0)),$C$2:$C$6)))