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.

Can you upload a sample file with dummy data,
using a free filehost, then post a link to it here?

In your sample, explain clearly what you want to happen

You can use this "easy-to-use" free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Bebe" wrote in message
...
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.

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


=SUMPRODUCT(((D2:D28=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
"Max" wrote:

Can you upload a sample file with dummy data,
using a free filehost, then post a link to it here?

In your sample, explain clearly what you want to happen

You can use this "easy-to-use" free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Bebe" wrote in message
...
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.

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.

"Max" wrote:

Can you upload a sample file with dummy data,
using a free filehost, then post a link to it here?

In your sample, explain clearly what you want to happen

You can use this "easy-to-use" free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Bebe" wrote in message
...
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.

On Jan 6, 7:39*pm, Bebe wrote:
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.



"Max" wrote:
Can you upload a sample file with dummy data,
using a free filehost, then post a link to it here?

In your sample, explain clearly what you want to happen

You can use this "easy-to-use" free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Bebe" wrote in message
...
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.

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:D$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
---

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

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

"Max" wrote:

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:D$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
---

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

"Max" wrote:

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:D$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
---

Welcome, glad it worked out ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Bebe" wrote in message
...
Wow, you are a genius, this is an elegant formula and it works perfectly,
Thank you.