"Complex" formula needed for a debtors ledger

Question

Alrighty so I'm still learning my way around excel and I'm trying to make a formula that will count how many deposits have been made that are less than half of the total invoice amount. They also need to be categorized by salesman and trading terms...An example of setup is as follows:Account Inv # Terms &nbsp; Due &nbsp; &nbsp; Amount 1/2 Dep. &nbsp; &nbsp; &nbsp; &nbsp;Paid &nbsp; Owing &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S/manClient 1 &nbsp; 8 &nbsp; &nbsp; &nbsp; COD &nbsp; &nbsp; 28/07 &nbsp; &nbsp;$100 &nbsp; &nbsp; &nbsp; &nbsp;=sum(E5/2) &nbsp; $40 &nbsp; &nbsp;=sum(E5-G5) &nbsp; BobClient 2 &nbsp; 32 &nbsp; &nbsp; 7 days &nbsp;31/07 &nbsp; &nbsp;$1000 &nbsp; &nbsp; &nbsp;=sum(E6/2) &nbsp; &nbsp;$0 &nbsp; &nbsp; =sum(E6-G6) &nbsp; AlanUnderneath all of it I've got little formula driven snapshots that show how many overdue clients each salesman has, how much each salesman is owed by clients, how much money clients owe us by terms, and now I'm attempting to have one for how many COD jobs were done without a salesman getting a full half deposit first.COD's with less than a 1/2 depositBob &nbsp; &nbsp; &nbsp; &nbsp;Alan &nbsp; &nbsp; &nbsp; &nbsp; Dave &nbsp; &nbsp; &nbsp; &nbsp; Ned? &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ? &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;?All I figured out was =SUMPRODUCT(--(PaidRange&lt;1/2DepositRange)). This, of course, got me a figure for how many jobs were completed without receiving at least half of their invoice total first, but I need that watered down to COD jobs only and for each salesman.

sergeibaklan · Accepted Answer

Hi Madeleine,&nbsp;You may continue filtering with SUMPRODUCT like=SUMPRODUCT((PaidRange&lt;1/2DepositRange)*(JobRange=Cod)*(Salesman=Name))or use COUNTIFS() to count with multiple criteria.&nbsp;&nbsp;

Forum Discussion

"Complex" formula needed for a debtors ledger

2 Replies

Resources