Hi,

I have a worksheet called holiday planner,
i use sumproduct for plotting the dates.
however i need to have another column for whereabouts.
also i use a custom if function to determine the whereabout since sumproduct
returns number.
my formula goes like this
SN = array for name
SW= array for whereabouts
SF=array for FROM
ST=array for TO
=((SUMPRODUCT(($G11=sn)*(sw="CRA")*(sf<=H$7)*(st= H$7)))+0.1)*((SUMPRODUCT(($G11=sn)*(sw="MNL")*(sf< =H$7)*(st=H$7)))+0.2)*((SUMPRODUCT(($G11=sn)*(sw= "PSL")*(sf<=H$7)*(st=H$7)))+0.3)*((SUMPRODUCT(($G 11=sn)*(sw="RTM")*(sf<=H$7)*(st=H$7)))+0.4)*((SUM PRODUCT(($G11=sn)*(sw="VLA")*(sf<=H$7)*(st=H$7))) +.5)*((SUMPRODUCT(($G11=sn)*(sw="WAL")*(sf<=H$7)*( st=H$7)))+.6)*((SUMPRODUCT(($G11=sn)*(sw="HAM")*( sf<=H$7)*(st=H$7)))+.7)*((SUMPRODUCT(($G11=sn)*(s w="BUD")*(sf<=H$7)*(st=H$7)))+.8)*((SUMPRODUCT(($ G11=sn)*(sw="TOI")*(sf<=H$7)*(st=H$7)))+.9)*((SUM PRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st=H$7))) +1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(s t=H$7)))+2)*((SUMPRODUCT(($G11=sn)*(sw="NIT")*(sf <=H$7)*(st=H$7)))+3)*((SUMPRODUCT(($G11=sn)*(sw=" PH")*(sf<=H$7)*(st=H$7)))+4)*((SUMPRODUCT(($G11=s n)*(sw="TC")*(sf<=H$7)*(st=H$7)))+5)*((SUMPRODUCT (($G11=sn)*(sw="TE")*(sf<=H$7)*(st=H$7)))+6)*((SU MPRODUCT(($G11=sn)*(sw="WOO")*(sf<=H$7)*(st=H$7)) )+7)*((SUMPRODUCT(($G11=sn)*(sw="WTF")*(sf<=H$7)*( st=H$7)))+8)

this works okey for me, however, as i progress in making my holiday planner,
since the rows grows larger, it gives me an error FORMULA TOO LONG.

Can anyone help me?

Thanks in advance