Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
CSE formula Over Sumproduct() | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Changing a Formula in Conditional Formatting | Excel Worksheet Functions | |||
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) |