average of cells with multiple criteria

Hi,

I hope someone will be able to give me some advice about this. I tried to run this formula

=AVERAGE(IF(((EF2:EF19998=3)*(IY2:IY19998=7)+(EG2:EG19998=3)*(IZ2:IZ19998=7)+(EH2:EH19998=3)*(JA2:JA19998=7)+(EI2:EI9998=3)*(JB2:JB19998=7)+(EJ2:EJ19998=3)*(JC2:JC19998=7)+(EK2:EK19998=3)*(JD2:JD19998=7)+(EL2:EL19998=3)*(JE2:JE19998=7)+(EM2:EM19998=3)*(JF2:JF19998=7)+(EN2:EN19998=3)*(JG2:JG19998=7)+(EO2:EO19998=3)*(JH2:JH19998=7)+(EP2:EP19998=3)*(JI2:JI19998=7)+(EQ2:EQ19998=3)*(JJ2:JJ19998=7)),Z2:Z19998))

Basically, i want to make an average of cell values for which at least one of the 12 pairs of criteria holds. It does not work, I suppose it is because its too long. If I reduce the length to some 3-4 pairs of criteria, it works fine.

Do you know some simpler formula that might work?

Thanks for reply!

Ok, here is simplified version what i want to do. Btw, it works because its in much smaller scale, so the question is, do you know some simpler formula that might work with much more data?

Its more like the union of those criteria pairs. So when whichever of those 12 criteria holds, the cell is used in calculation of average (but the cell must be added to the calculation only once).

Union ∪ of two datasets is set of data that are members of at least one of it (you can call it or). So i want the formula to do this, criteria pair1 ∪ pair2 ∪ pair 3 ∪ .... => if at least one of these holds, pick the corresponding cell (BUT ONLY ONCE) and add it to the average calculation.

You can add there some condition "if false", but it doesnt change the result of the formula anyway, because some criteria holds at least once.