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