changing a Sumproduct formula please

[Tester]

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

 

[Martin Fishlock]

Try:
=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)

=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.

 

[Tester]

Thank you, it works fine

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]

Glad to hear that, Tester.