Sumproduct and Max value

[James]

HELP!

I've been searchign thru the database trying to find out how to pull from
one tab the largest value of an array.

I need to find the longest wait time per month per group. Originally I had

=SUMPRODUCT(--((Skill_Calc!$A$2:$A$65499)=$A46),--((Skill_Calc!$C$2:$C$65499)=$E$4),(Skill_Calc!$L$2:$L$65499))

Where A46 is "01Month" and E4 is the group name, but I realized I am not
summing the values--I need the max.

This doesn't work.

=SUMPRODUCT(--((Skill_Calc!$A$2:$A$65499)=$A46),--((Skill_Calc!$C$2:$C$65499)=$E$4),MAXA(Skill_Calc!$L$2:$L$65499))

 

[T. Valko]

Try this array** formula:

=MAX((Skill_Calc!$A$2:$A$65499=$A46)*(Skill_Calc!$C$2:$C$65499=$E$4)*Skill_Calc!$L$2:$L$65499)

** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Teethless mama]

=MAX(IF((Skill_Calc!$A$2:$A$65499=$A46)*(Skill_Calc!$C$2:$C$65499=$E$4),Skill_Calc!$L$2:$L$65499))

ctrl+shift+enter, not just enter

 

[James]

Thanks! That did the trick! Happy Friday to me indeed...