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(OLDSHEE T!$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

One way is just to use this, and copy down:

=SUMPRODUCT((OLDSHEET!$A$2:$A$10000=A2)*(OLDSHEET! $B$2:$B$10000=B2)*(OLDSHEET!$C$2:$C$10000=C2),OLDS HEET!$D$2:$D$10000)

The above expression will do away with having to create col E in OldSheet
which was your query. It'll simply return zero for unmatched cases, which is
usually an acceptable return, hence avoiding the need for additional
error-trapping. As the ranges are large, re-calcs will be intensive. You
might wish to switch it to manual calc mode, and press F9 to recalc only when
necessary.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"FARAZ QURESHI" wrote:
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(OLDSHEE T!$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?

Wow MAX!

U'r the Best!

Quite a long time since I last heard from you.
--

Best Regards,
FARAZ A. QURESHI


"Max" wrote:

One way is just to use this, and copy down:

=SUMPRODUCT((OLDSHEET!$A$2:$A$10000=A2)*(OLDSHEET! $B$2:$B$10000=B2)*(OLDSHEET!$C$2:$C$10000=C2),OLDS HEET!$D$2:$D$10000)

The above expression will do away with having to create col E in OldSheet
which was your query. It'll simply return zero for unmatched cases, which is
usually an acceptable return, hence avoiding the need for additional
error-trapping. As the ranges are large, re-calcs will be intensive. You
might wish to switch it to manual calc mode, and press F9 to recalc only when
necessary.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"FARAZ QURESHI" wrote:
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(OLDSHEE T!$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?

Welcome, Faraz
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Hi
=Sumproduct((Oldsheet!$A2:$A100=$A2)*(Oldsheet($B$ 2:$B$100=$B2)
*(OldSheet$C$2:$C$100=$C2)*$D2:$D100)

--
Regards
Roger Govier

"FARAZ QURESHI" wrote in message
...
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(OLDSHEE T!$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