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

 

[chandrashekar]

Hi,

Even Max function is not working I changed the data but I am not getting max value.

Thanks

Chandra Shekar B

 

[jasonb75]

Does the array give a result?

 

[chandrashekar]

Yes, Array will give the result.

 

[jasonb75]

Then something in your data is incompatible with sumproduct, you will have to use the array.

 

[chandrashekar]

Hi,

Its ok, I will use array funcion to do this task. Thanks!

 

[T. Valko]

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

For a conditional MIN try this array formula**:

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

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

 

[chandrashekar]

Hi,

Thanks a lot for your support.

Regards,

Chandra Shekar B