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

House Number	Date	Yield
31	01-06-16	10
36	02-06-16	9
37	01-06-16	8
1	03-06-16	9
5	01-06-16	10

<tbody>
</tbody>

 

[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)))