SUMIF/AVERAGEIF with multiple ranges

This is what i was using,

=AVERAGEIF($B$198:$B$240,B254,$C$198:$C$240)

I want to add more sets of ranges and average ranges like this,

=AVERAGEIF(($B$6:$B$48,$B$70:$B$112,$B$134:$B$176,$B$198:$B$240),B262,($C$6:$C$48,$C$70:$C$112,$C$134:$C$176,$C$198:$C$240))

Which of course returns #VALUE!

Help Please!!!

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

Please Login or Register  to view this content.

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

Simple enough to adjust:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.