Find Average using Sumproduct
 

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...
--
Karthi

Find Average using Sumproduct
 

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.

--
Biff
Microsoft Excel MVP


"Karthik" wrote in message
...
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...
--
Karthi

Find Average using Sumproduct
 

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))

"Karthik" wrote:

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...
--
Karthi

Find Average using Sumproduct
 

Try an Array formula:
=AVERAGE(IF((B4:AT4=C113)*(B5:AT5=C115),B7:AT7))
Micky



"Karthik" wrote:

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...
--
Karthi