Sumproduct with Average

[chandrashekar]

Hi,

How to get average based on multi condition. I am getting count correctly but I need average. Average Column is at C. E2 & F2 mentioned in formula is DATE and D2 is Company Name and I need avergage based on this conditions.

Thanks in advance.

=SUMPRODUCT(--(A2:A2000>=E2);--(A2:A2000<=F2);--(B2:B2000=D2))

Regards,

Chandra Shekar B

 

[AlphaFrog]

Try something like this...

=SUMPRODUCT(--(A2:A2000>=E2);--(A2:A2000<=F2);--(B2:B2000=D2);C2:C2000)/
 SUMPRODUCT(--(A2:A2000>=E2);--(A2:A2000<=F2);--(B2:B2000=D2))

 

[jasonb75]

Try

=AVERAGE(IF((A2:A2000>=E2)*(A2:A2000<=F2)*(B2:B2000=D2);C2:C2000))

Must be array confirmed using Shift Ctrl Enter

Or with excel 2007 or newer try

=AVERAGEIFS(C2:C2000;A2:A2000;">="&E2;A2:A2000;"<="&F2;B2:B2000;D2)

 

[chandrashekar]

Thanks a lot for both of u.

Regards,

Chandra Shekar B

 

[chandrashekar]

Hi,

One more question how to get min & max value using sumproduct.

Thanks in Advance

Regards,

Chandra Shekar B

 

[jasonb75]

Use the array method

=AVERAGE(IF((A2:A2000>=E2)*(A2:A2000<=F2)*(B2:B2000=D2);C2:C2000))

Must be array confirmed using Shift Ctrl Enter

Change AVERAGE to MIN or MAX.

 

[chandrashekar]

Please let me know can we use sumproduct instead of array?

 

[jasonb75]

Seems pointless as both serve the same purpose

=sumproduct(max((A2:A2000>=E2)*(A2:A2000<=F2)*(B2:B2000=D2)*C2:C2000))

 

[chandrashekar]

Thanks a lot

 

[chandrashekar]

Hi,

I tried MIN function which is not working. Please let me know what is the problem is.

=SUMPRODUCT(MIN(($A$2:$A$2000>=E2)*($A$2:$A$2000<=F2)*($B$2:$B$2000=D2)*$C$2:$C$2000))

Thanks

Chandra Shekar B