Find Average using Sumproduct

  • Thread starter Thread starter Karthik
  • Start date Start date
K

Karthik

Is there a way to find average based on multiple conditions.
I've been using Sumproduct formula to Sum all the cells but not sure how to
use it find average.

=SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115),$B7:$AT7)

Thanks for your help in advance...
 
Try this array formula** :

=AVERAGE(IF($B$4:$AT$4=$C$113,IF($B$5:$AT$5=$C$115,$B7:$AT7)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Hi

=SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115),$B7:$AT7)/SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115))
 
Try an Array formula:
=AVERAGE(IF((B4:AT4=C113)*(B5:AT5=C115),B7:AT7))
Micky
 
Back
Top