Season's greetings
Hi guys I have this sumproduct formula on several cells:
=SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AI18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)+SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AK18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)+SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AM18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)
It is basically looking for three criteria on one common column and returning the value which are on the same column as well. I did not use VLOOKUP for fear of getting an N/A error.
My request is whether this can be condensed. It is quite long, and I am planning to use it with another set of arguments, and in excel2003 we only 1,024 characters I believe. So any suggestions.
Thank you
Hi guys I have this sumproduct formula on several cells:
=SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AI18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)+SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AK18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)+SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AM18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)
It is basically looking for three criteria on one common column and returning the value which are on the same column as well. I did not use VLOOKUP for fear of getting an N/A error.
My request is whether this can be condensed. It is quite long, and I am planning to use it with another set of arguments, and in excel2003 we only 1,024 characters I believe. So any suggestions.
Thank you
Last edited: