Average with multiple criterias using sumproduct

Hello forum,

I am trying to identify an average based on multiple criterias using sumproduct and frequency.

I have previously tried (with the help of someone from this forum) to use an array formula for average that looked somewhere around the lines of : average(if((criteria range=criteria 1)*(criteria range2=criteria 1),range of average) as an array formula, but unfortunately my database is too large for this formula to be used effectively so I am trying out for alternatives.

I have calculated the sumproduct of my range in order to identify the summed "time" for each "stage" and "year" on column H, and I want to divide it by the number of unique times the values in column D appear for each year& time. My sumproduct formula takes into account duplicate values as well, and I only want it to take into account values that are not duplicated (e.g. sumproduct for stage A and year 2008 should be 7 because ID 7000 is duplicated, not 11 as it currently shows). I tried using the frequency formula, but frankly I do not think I am doing it right.

Appreciate any help I could get!! Thank you.

=averageifs($g$4:$g$15,$e$4:$e$15,e4,$f$4:$f$15,f4)

Hi Tim,

I have already tried using the averageifs formula as previously stated, however my actual database is extremely huge and the formula takes 30+ minutes to load, hence I am trying for alternatives using the sumproduct/frequency combo. Is there any alternative to calculating average given multiple criteria for large datasets that does not include =averageifs( formula?

Thank you!

averageifs is more more efficient than sumproduct/frequency combo

=SUMPRODUCT(($E$4:$E$15=E4)*($F$4:$F$15=F4),$G$4:$G$15)/SUMPRODUCT(($E$4:$E$15=E4)*($F$4:$F$15=F4))

or

=SUMIFS($G$4:$G$15,$E$4:$E$15,E4,$F$4:$F$15,F4)/COUNTIFS($E$4:$E$15,E4,$F$4:$F$15,F4)

but would be very surprised if they were quicker than the averageifs formula

A pivot table is also an option, which is likely to be computationally quicker

Thank you both for replying. I did not manage to replicate these formulas to my actual database, mainly because the "id" column should also be taken into account (the original reason why I tried using a frequency formula). A row should only be counted once towards the average should it have more than one line with the same "ID" for the "stage" and "year". I realized I failed to provide this scenario in my initial example, so I added this scenario in the file below - apologies for that, I realized it as I was trying to implement your solutions (

Copied Tim's formula on column H, and Davsth's on columns I & J.

=SUMPRODUCT(($E$4:$E$17=E4)*($F$4:$F$17=F4)*(MATCH($C$4:$C$17,$C$1:$C$17,)=ROW($D$4:$D$17))*$G$4:$G$17)/SUMPRODUCT(($E$4:$E$17=E4)*($F$4:$F$17=F4)*(MATCH($C$4:$C$17,$C$1:$C$17,)=ROW($D$4:$D$17)))
with help column

Originally Posted by tim201110

=SUMPRODUCT(($E$4:$E$17=E4)*($F$4:$F$17=F4)*(MATCH($C$4:$C$17,$C$1:$C$17,)=ROW($D$4:$D$17))*$G$4:$G$17)/SUMPRODUCT(($E$4:$E$17=E4)*($F$4:$F$17=F4)*(MATCH($C$4:$C$17,$C$1:$C$17,)=ROW($D$4:$D$17)))
with help column

Thank you Tim, that solved it, I was able to apply it to my database! I am curious if this could have been solved using a variation of the averageifs formula, or is this the most efficient way of calculating this formula?

I will mark the thread as closed, than you both for chipping in Have a great day/night.

=AVERAGEIFS($G$4:$G$17,$E$4:$E$17,E4,$F$4:$F$17,F4,$B$4:$B$17,1)
one more help column