SHORTEN A FORMULA - AVOIDING AN EXTRA COLUMN

[FARAZ QURESHI]

I have to update a list of clients every week as per the following format:
Column A Branch Code
Column B Account Number
Column C Product Code
Column D Amount

Now, every week when I receive a new list I need to fist enter a formula in
COLUMN E in the old sheet as =+A2&"-"&B2&"-"&C2 copied in all the rows and
then workout the account-wise differences by entering the following type of
formula in the new sheet's COLUMN E for former amounts:

=+IF(ISNA(VLOOKUP(A2&"-"&B2&"-"&C2,OLDSHEET!$E$2:$E$10000,1,0)),"",SUMIF(OLDSHEET!$E$2:$E$10000,A2&"-"&B2&"-"&C2,OLDSHEET!$D$2:$D$10000))

so as to calculate the total amount pertaining to the respective branch's
client's said product.

Any idea how to shorten such a formula and carryout a similar exercise
without creating the EXTRA COLUMN "E" in the Old Sheet, i.e. inserting
formulas like:

=+A2&"-"&B2&"-"&C2?

Thanx in advance,

Best Regards,
FARAZ A. QURESHI

 

[CurlyDave]

Try
=SUMPRODUCT((A6:A27=A1)*(B6:B27=B1)*(C6:C27=C1)*(D627))

or

=SUM(IF((A6:A500=A1)*(B6:B500=B1)*(C6:C500=C1),D6500)) -this
is an array formula must be entered with Ctrl+Shift+Enter

Here is an example

http://sites.google.com/site/davesexcel/Home/forum-examples/SumProductSumIf.xls?attredirects=0