Split payments

[Bebe]

I need to make a spreadsheet to keep track of my commission. Payments are
made every 5 days to me (5th,10th,15th,20th,25th) Each client makes 2
payments 1 month apart. I need a way to keep track of what I should be paid
on each date and the names and amounts of clients I should be paid for. How
can I go about doing this? Any help will e appreciated in advance.

 

[Bebe]

http://freefilehosting.net/download/43jk2


=SUMPRODUCT(((D228=A2+(E2:E28=A2))*(I12:I28))) but I think the excel gets
confused or it is not the right formula, right now I have everything in the B
column added up manually and manually entered but I was trying to get it to
auto add for me in appropriate date box, I am open to a complete re-design of
sheet if need be. Thank you for your help

I was using

 

[Bebe]

Sorry Max, I didnt realize about putting the explanation on the website. I
need for a formula to scan column d and look for a date and when it finds it
puts all the dollar amounts from column I into that cell in column B, then
scan column E and do the same, add all dollar amounts into column b cell for
that date. Sumproduct will work fine for first 2 months or so then gets
confused and skips or adds noting at all.

 

[B~O~B]

Sorry Max, I didnt realize about putting the explanation on the website. I
need for a formula to scan column d and look for a date and when it findsit
puts all the dollar amounts from column I into that cell in column B, then
scan column E and do the same, add all dollar amounts into column b cell for
that date. Sumproduct will work fine for first 2 months or so then gets
confused and skips or adds noting at all.








- Show quoted text -

try using =SUMIFS($I$2:$I$30,$D$2:$D$30,$A2)+SUMIFS($I$2:$I$30,$E$2:$E
$30,$A2)

Also, check you date date you have 5-nov-09 for dates that i think you
-08...

 

[Max]

need a formula to scan column d and look for a date and when it finds it

puts all the dollar amounts from column I into that cell in column B, then
scan column E and do the same, add all dollar amounts into column b cell for
that date.

In Sheet2,
Try using 2 sumproducts (1 for col D, the other for col C), like this
In B2
=SUMPRODUCT(--(TEXT(D$2$30,"ddmmm")=TEXT(A2,"ddmmm")),I$2:I$30)+SUMPRODUCT(--(TEXT(E$2:E$30,"ddmmm")=TEXT(A2,"ddmmm")),I$2:I$30)
Copy down as required. It should give the results that you're after. Since
you have intervening cells for "subtotals", eg in B7, B13, etc, you could
copy B2, then do a CTRL-select on the cells excluding these "subtotals", then
right-click > paste special > Check Formulas > OK, to copy B2 down at one go.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[Max]

Try using 2 sumproducts (1 for col D, the other for col C),
It should read above as col E, not col C.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[Bebe]

Thank you Max and B~O~B, I will try both. I appreciate everyones help here
truly.

 

[Bebe]

Wow, you are a genius, this is an elegant formula and it works perfectly,
Thank you.

 

[Max]

Welcome, glad it worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik