need to cut the sumproduct formula short !

[xcruc1at3r]

=SUMPRODUCT(((CCO!$E$5:$E$600="SIN")*(CCO!$C$5:$C$600="H40"))+((CCO!$E$5:$E$600="CMB")*(CCO!$F$5:$F$600="CMB")*(CCO!$C$5:$C$600="H40"))+((CCO!$E$5:$E$600="CMB")*(CCO!$F$5:$F$600="CHT")*(CCO!$C$5:$C$600="H40"))+((CCO!$E$5:$E$600="CMB")*(CCO!$F$5:$F$600="VME")*(CCO!$C$5:$C$600="H40"))+((CCO!$E$5:$E$600="LCB")*(CCO!$C$5:$C$600="H40"))-((CCO!$E$5:$E$600="SIN")*(CCO!$F$5:$F$600="MEL")*(CCO!$C$5:$C$600="H40"))-((CCO!$E$5:$E$600="SIN")*(CCO!$F$5:$F$600="SYD")*(CCO!$C$5:$C$600="H40"))-((CCO!$E$5:$E$600="SIN")*(CCO!$F$5:$F$600="ADL")*(CCO!$C$5:$C$600="H40"))-((CCO!$E$5:$E$600="SIN")*(CCO!$F$5:$F$600="BNQ")*(CCO!$C$5:$C$600="H40"))-((CCO!$F$5:$F$600="BRB")*(CCO!$C$5:$C$600="H40"))-((CCO!$F$5:$F$600="BNL")*(CCO!$C$5:$C$600="H40"))-((CCO!$F$5:$F$600="RHD")*(CCO!$C$5:$C$600="H40"))+((CCO!$F$5:$F$600="DHK")*(CCO!$C$5:$C$600="H40")))


is there any way to trim this formula !!

 

[Hui]

Are you aware some of the arrays have 2 elements and some have 3 ?


You could possibly use some of the D functions to simplify this

eg DCount or DSum

 

[xcruc1at3r]

yes i know i have written this dam formula for the Vessel Export Recap some are based o to 2 arrays and some on 3....i have never used D Functions... but there is always a 1st time for everything :D when it comes to learning...

 

[xcruc1at3r]

is there any possibility based on having a fix Sheet Name! cause we create recap for every vessel the cco code is for VEssel Named Chicago... like that JPN for Japan...so i cannot define the name for the formula because of the sheet names....

 

[xcruc1at3r]

is there any link related to D functions?

 

[Hui]

I'd start with

http://office.microsoft.com/en-us/excel-help/dsum-HP005209069.aspx

&

http://www.ozgrid.com/Excel/sum-if.htm