changing a Sumproduct formula please
 

This is my current formula, found in sheet "Creditors" across 12 columns:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3),'Purchase Ledger'!$J$3:$J$2000)

At present it checks the column E contents of my sheet "Purchase Ledger" to
see if they match the Creditor(by number), if so, it then checks the month
number in column F of "Purchase Ledger" and if that matches the column
number the formula is in it goes on to total the outstanding amount owed ot
the creditor.

I would like to split this into 2, showing the total of positive numbers and
the total of negative numbers so that i can clearly see total invoices
received and total payments made in each month.

Please could you vary the above formula for me/ i know it will have to go in
2 separate cells to show both figures.
Thank you
Chris

changing a Sumproduct formula please
 

One way, try these revisions ..

Total of positive numbers:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3)*('Purchase
Ledger'!$J$3:$J$2000=0),'Purchase Ledger'!$J$3:$J$2000)

Total of negative numbers:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3)*('Purchase
Ledger'!$J$3:$J$2000<0),'Purchase Ledger'!$J$3:$J$2000)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tester" wrote:
This is my current formula, found in sheet "Creditors" across 12 columns:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3),'Purchase Ledger'!$J$3:$J$2000)

At present it checks the column E contents of my sheet "Purchase Ledger" to
see if they match the Creditor(by number), if so, it then checks the month
number in column F of "Purchase Ledger" and if that matches the column
number the formula is in it goes on to total the outstanding amount owed ot
the creditor.

I would like to split this into 2, showing the total of positive numbers and
the total of negative numbers so that i can clearly see total invoices
received and total payments made in each month.

Please could you vary the above formula for me/ i know it will have to go in
2 separate cells to show both figures.
Thank you
Chris

changing a Sumproduct formula please
 

Try:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3),--('Purchase
Ledger'!$J$3:$J$20000),'Purchase Ledger'!$J$3:$J$2000)

=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3),--('Purchase
Ledger'!$J$3:$J$2000<0),'Purchase Ledger'!$J$3:$J$2000)

Although it may be more efficient to use an key column in the purchase
ledger of e3&":"&f3 to cut down on the calculations.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Tester" wrote:

This is my current formula, found in sheet "Creditors" across 12 columns:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3),'Purchase Ledger'!$J$3:$J$2000)

At present it checks the column E contents of my sheet "Purchase Ledger" to
see if they match the Creditor(by number), if so, it then checks the month
number in column F of "Purchase Ledger" and if that matches the column
number the formula is in it goes on to total the outstanding amount owed ot
the creditor.

I would like to split this into 2, showing the total of positive numbers and
the total of negative numbers so that i can clearly see total invoices
received and total payments made in each month.

Please could you vary the above formula for me/ i know it will have to go in
2 separate cells to show both figures.
Thank you
Chris

changing a Sumproduct formula please
 

Thank you, it works fine

"Max" wrote in message
...
One way, try these revisions ..

Total of positive numbers:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3)*('Purchase
Ledger'!$J$3:$J$2000=0),'Purchase Ledger'!$J$3:$J$2000)

Total of negative numbers:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3)*('Purchase
Ledger'!$J$3:$J$2000<0),'Purchase Ledger'!$J$3:$J$2000)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tester" wrote:
This is my current formula, found in sheet "Creditors" across 12 columns:
=SUMPRODUCT(--('Purchase Ledger'!$E$3:$E$2000=Creditors!$B5),--('Purchase
Ledger'!$F$3:$F$2000=Creditors!G$3),'Purchase Ledger'!$J$3:$J$2000)

At present it checks the column E contents of my sheet "Purchase Ledger"
to
see if they match the Creditor(by number), if so, it then checks the
month
number in column F of "Purchase Ledger" and if that matches the column
number the formula is in it goes on to total the outstanding amount owed
ot
the creditor.

I would like to split this into 2, showing the total of positive numbers
and
the total of negative numbers so that i can clearly see total invoices
received and total payments made in each month.

Please could you vary the above formula for me/ i know it will have to go
in
2 separate cells to show both figures.
Thank you
Chris

changing a Sumproduct formula please
 

Glad to hear that, Tester.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tester" wrote in message
...
Thank you, it works fine