Help with too long formula

C

calebmichaud

Hi.....trying to figure out a way to get this formula smaller so that
it will fit within excel requirements....and suggestions?

=IF(D5="ATL",SUMIF($D$5:$D$40,"=ATL",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="ATL")),
IF(D5="BLT",SUMIF($D$5:$D$40,"=BLT",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="BLT")),
IF(D5="CHI",SUMIF($D$5:$D$40,"=CHI",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="CHI")),
IF(D5="CIN",SUMIF($D$5:$D$40,"=CIN",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="CIN")),
IF(D5="DAL",SUMIF($D$5:$D$40,"=DAL",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="DAL")),
IF(D5="DEN",SUMIF($D$5:$D$40,"=DEN",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="DEN")),
IF(D5="DET",SUMIF($D$5:$D$40,"=DET",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="DET")),
IF(D5="FLA",SUMIF($D$5:$D$40,"=FLA",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="FLA")),
IF(D5="KAN",SUMIF($D$5:$D$40,"=KAN",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="KAN")),
IF(D5="LOS",SUMIF($D$5:$D$40,"=LOS",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="LOS")),
IF(D5="MIN",SUMIF($D$5:$D$40,"=MIN",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="MIN")),
IF(D5="NOL",SUMIF($D$5:$D$40,"=NOL",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="NOL")),
IF(D5="NWJ",SUMIF($D$5:$D$40,"=NWJ",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="NWJ")),
IF(D5="NYK",SUMIF($D$5:$D$40,"=NYK",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="NYK")),
IF(D5="PHI",SUMIF($D$5:$D$40,"=PHI",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="PHI")),
IF(D5="SEA",SUMIF($D$5:$D$40,"=SEA",$G$5:$G$40)/SUMPRODUCT(($G$5:$G
$40>0)*($D$5:$D$40="SEA")))))))))))))))))
 
D

Dave Peterson

Maybe...

=IF(OR(D5={"atl","blt","chi","cin","dal","den","det","fla","kan","los","min"}),
SUMIF($D$5:$D$40,D5,$G$5:$G$40)/SUMPRODUCT(($G$5:$G$40>0)*($D$5:$D$40=D5)),
"not one of those")

(I added the "else" portion to the formula.)

But if those are the only values (along with an empty cell) that can go in D5
(protected by data|validation???), then maybe:

=if(d5="","",
SUMIF($D$5:$D$40,D5,$G$5:$G$40)/SUMPRODUCT(($G$5:$G$40>0)*($D$5:$D$40=D5)))
 
B

Bernard Liengme

Have you tried
=SUMPRODUCT(($D$5:$D$40=D5)*$G$5:$G$40)/SUMPRODUCT(($G$5:$G$40>0)*($D$5:$D$40=D5)),
best wishes
 
C

calebmichaud

Both of those examples work great!!! Thank you SO much. I really
apprecaite your help.

Caleb
 
Top