Transactions between deals

Hi Guys,
What I want to do is this:
I have 1 sheet in excel that holds records(among others) TRANSACTION AMOUNTS,TRANSACTIONS DATES, DEAL DATE, NEXT DEAL DATE.

I have created the NEXT DEAL DATE because I want to be able to find transactions between those dates and sum them up so that each DEAL ID will hold the amount that was collected from it.
How does it work?

1.) The deal date holds the date of the deal, and the next deal date column holds the date of the next deal(if there is one) or today's date if there is no other deal after that - in that way I am able to sum all collections between that date and today.
2.) I want to give the last deal the credit of the transaction, so that it if I have a transaction between 2 deals, the last one will get the amount on its row.

Because the table is arranged by account number and then by deal dates, each transaction needs to be looping on every DEAL_date and find the latest DEAL and attribute the amount on that row to that deal.

Any ideas friends?

Thanks and have a good day

Can you post the worksheet or an example?

Thanks for the reply QEJohn.

test_deals_collections.xlsx

The empty field could just be 1/1/2012.
The important thing is to have the summed amounts attributed to each deal_ID

Appreciate the help John (:

I'm still not sure what you are asking for. I must have read your question 100 times by now. How close am I here?
You want a running total on each line.
That total should be the sum of all transactions on transaction dates dates b/w deal date and next deal date.
This should repeat for each account.

Please clarify.

Sorry for being unclear.

You are correct,
But I want the sums to be on the DEAL_ID row, so I know how much each deal has collected.
and yes, that total should be a running total that sums all transaction amounts between deals for each account.

Thanks.

Is this what you are looking to do?

test_deals_collections (1).xlsx

Thanks for the quick response.

It's very close.

I only need to check if the transaction date is between the deal date and the next date and only then to sum it up.

Great work!

Originally Posted by QEJohn

Is this what you are looking to do?

Attachment 196879

Hi John,
I appreciate the help.
Do you have a clue about the dates?
I mean checking if the transaction date is between the deal date and the next deal date and if so only then to sum it up to the account's total.

Thanks.

Please open the attachement, fill in the answers you would like to receive, then re-post. This way I can work toward something.

Thank you.

test_deals_collections (1).xlsx

Thanks Man.

Also It's 40 and 30 for the last deals. not 30 70.

if deal 111113 should be 40, why is deal 111111 not 110? Reason being:
111114 trans date = 111113 deal date
111111 trans date = 111111 deal date
It seems like your rules are not consistent.
In any case, See the attached.
test_deals_collections (1) (1).xlsx
I believe it's what you're looking for if you want the trans date to be BETWEEN the deal dates. If you want the trans date to be BETWEEN OR EQUAL TO the deal dates, insert the following formula into G2 and drag down:
=SUMIFS($C$2:$C$6,$B$2:$B$6,$B2,$D$2:$D$6,">="&$E2,$D$2:$D$6,"<="&$F2)

Should be good...
maybe...

and remember that all your dates should be in mm/dd/yy (or yyyy) format. the original file has dd/mm/yyyy, which i don't typically work with, so i'm not sure on excel's recognition of them.

You just made my day!
Thanks a lot for your help!
You're great! (:

You have made a friend in NY! (: