AverageIF using multiple criteria in different columns in multiple worksheets

I'm working in a workbook with two worksheets: one summary tab and one data tab (SQL Results). The data tab is attached and contains the values that the summary tab is aggregating using several SUMPRODUCT formulas.

In the summary tab, the SUMPRODUCT formulas are returning counts of records that meet multiple criteria, as shown in this formula:

cell B5 contains =SUMPRODUCT(('SQL Results'!L:L="I")*('SQL Results'!F:F="201340")*('SQL Results'!I:I="Y")*('SQL Results'!P:P>0.949)*('SQL Results'!T:T<1))
Result is 8 records.


On the Summary tab, I am trying to use AVERAGEIF or IF(...(AVERAGE(...)) to calculate the average of the amounts in Column Y (FUND1_PAID) IF the criteria in the SUMPRODUCT formula are met. My formula seems to be averaging the entire Column Y despite my limiting attempts:

=IF((SUMPRODUCT(('SQL Results'!L:L="I")*('SQL Results'!J:J="1")*('SQL Results'!F:F="201340")*('SQL Results'!I:I="Y")*('SQL Results'!P:P>0.949)*('SQL Results'!T:T<1))),(AVERAGE('SQL Results'!Y:Y)))

This array version isn't quite doing the trick, either. I confirmed with CTRL+SHIFT_ENTER:

=AVERAGE(IF(('SQL Results'!L:L="I")*('SQL Results'!J:J="1")*('SQL Results'!F:F="201340")*('SQL Results'!I:I="Y")*('SQL Results'!P:P>0.949)*('SQL Results'!T:T<1),'SQL Results'!Y:Y))

Any thoughts?

Thank you!

Yeah, I'm bumping up against that resource issue now, apparently. I've gotten some "Excel is out of resources" messages. Maybe I need to rethink the summary tab.

I tried daffodil11's suggestion, but got a #VALUE error. Here's what I entered:

=SUMPRODUCT(('SQL Results'!L:L="I")*('SQL Results'!F:F="201340")*('SQL Results'!I:I="Y")*('SQL Results'!P:P>0.949)*('SQL Results'!T:T<1)*('SQL Results'!Y:Y))/SUMPRODUCT(('SQL Results'!L:L="I")*('SQL Results'!F:F="201340")*('SQL Results'!I:I="Y")*('SQL Results'!P:P>0.949)*('SQL Results'!T:T<1))

Excel help mentions disparate data types, so maybe my Y and I indicators are fighting with my numeric values?


My original thought with the summary tab was to tag each record in the data tab with a Block #, depending on the values in the F, L, I, P and T columns. Each record would belong in only one Block and then I could do simple summaries and counts based on the Block indicators in the data tab. I couldn't figure out how to make that work without manually filtering for each of the 32+ Blocks that the data set requires. I wonder if a very long nested IF statement would allow me to build Block #s for each row.

Thank you both for answering, by the way! I'm enjoying my first day on the forum

That's exactly the answer I was expecting. And now I know about the AVERAGEIFS forumula. Thanks very much for your help - that got me over the hump, and I can get back to working on the rest of this model. Have a great weekend!