Sumproduct and Max value
 

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

Sumproduct and Max value
 

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

"James" wrote in message
...
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))

Sumproduct and Max value
 

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

ctrl+shift+enter, not just enter


"James" wrote:

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

Sumproduct and Max value
 

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

"Teethless mama" wrote:

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

ctrl+shift+enter, not just enter


"James" wrote:

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