Help with too long formula

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

 

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

 

[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

 

[calebmichaud]

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

Caleb