Array formula which can average and sum two different criteria

**CFlack8472** · 07-05-2012, 09:08 AM

Hi all

I have been working on an array formula but can't seem to crack it - I need it to do what the attached Pivot table does (that should make it easier) I need a formula which can accomplish the same thing as the pivot table as I need to edit the formula to be able to delpoy it elsewhere.

As you can see from the pivot table it needs to get me an average of people who went to a specific university (from column A, got to a technical interview and the respective scores for that university)

As a result of that calculation I will know what the average score of people who got a technical interview is depending on their university.

If anyone knows how to finish off my formula in the worksheet "interview analysis" column L I would be very grateful.

I was almost there but it's missing a few people and I don't know how to average it off.

I have removed the other sheets which had peoples personal information in hence the large number of # values.

**NBVC** · 07-05-2012, 09:28 AM

Try:

Please Login or Register  to view this content.

copied down

**CFlack8472** · 07-05-2012, 09:51 AM

You're a hero I don't know how the hell you do that.

**NBVC** · 07-05-2012, 10:16 AM

Sumproduct() can be used to SUM or COUNT based on conditions... similar to SUMIF and COUNTIF, but you can use multiple conditions with SUMPRODUCT(). In Excel 2007 and later they added SUMIFS and COUNTIFS which allow multiple conditions, and even AVERAGEIF and AVERAGEIFS, which would make this an easier task....

Read here on Sumproduct() to see how it can be used for these tasks.

So I have a SUMPRODUCT() to sum divided by a SUMPRODUCT() to count.... the initial LOOKUP(9.99999e+307,CHOOSE({1,2},0,Your_formula)) is an error trap trick.. it returns a 0 if the averaging returns a #Div/0! error due to denominator being 0 (i.e no matches).