Help with too long formula

  • Thread starter Thread starter calebmichaud
  • Start date Start date
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")))))))))))))))))
 
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)))
 
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
 
Both of those examples work great!!! Thank you SO much. I really
apprecaite your help.

Caleb
 
Back
Top