SUMIF/AVERAGEIF with multiple ranges

I think AVERAGEIFS() only allows one Range to Average, but you can have multiple criterias and criteria ranges...

You may need to Average each range to each condition separately, then average all the averages.. or sum them and then average them all...

If as it seems you have a pattern to the data, ie 43 rows "on" 21 rows "off" then you could possibly use AVERAGE array

=AVERAGE(IF((MOD(ROW($B$6:$B$240)-ROW($B$6),64)<=42)*($B$6:$B$240=B262),$C$6:$C$240))
confirmed with CTRL + SHIFT + ENTER

also, if using XL2007 please update your profile accordingly as this can help people tailor solutions appropriately...

Simple enough to adjust:

=AVERAGE(IF((MOD(ROW($B$6:$B$240)-ROW($B$6),64)<=42)*($B$6:$B$240=B262)*($C$6:$C$240=C262),$C$6:$C$240))
confirmed with CTRL + SHIFT + ENTER

though in retrospect it would be better to split the tests into separate IFs to avoid unnecessary computations:

=AVERAGE(IF(MOD(ROW($B$6:$B$240)-ROW($B$6),64)<=42,IF($B$6:$B$240=B262,IF($C$6:$C$240=C262,$C$6:$C$240))))
confirmed with CTRL + SHIFT + ENTER