Help with Looking up transactions from within a date range, please.

K

Kimbersan

New member
Joined
Oct 16, 2015
Messages
11
Reaction score
0
Points
0
Bear with me please while I try to explain what I am doing. I have found two free Excel budgeting templates online that I am trying to marry together to become an awesome tool for my personal budgeting ability. First, I am using a dynamic template that has a Year-In-Review Budget separated monthly. Then I can input all my account Transactions on another Sheet. Then, I can run a Report on a third Sheet to compare Budget vs. Actual numbers. Totally awesome.....but incomplete for me. I have to have my budget broken down to weekly segments so that I can more accurately record my Income and plan for Expenses. So, I searched and found a single Sheet Weekly budget that will also show Budget numbers, Actual Numbers, and Difference between. I have copied this weekly into a new Sheet of the other one, and am working to link all the pages together.

Here is the specific need that I require help with: To automatically pull the Actual Numbers from the Transactions Sheet into the Weekly Sheet, and SUM them, as they are being entered into the Transactions. I already have the equation for the SUM because the Report Sheet has them already linked to show for the whole Month. However, I need to further reduce (or Filter, if you will) the Transactions that occur within the week.

On the Weekly Sheet labeled "October", C6 has the first Date of the week, and D6 has the second date. I need the equation to identify all the transactions that occur on-between these two dates, then it should perform this calculation equation: =SUMIF(Transactions!F:F,"="&A15,Transactions!J:J)

In words, A15 is the Category "Groceries", and I need to know how much I spent during the week of October 4th through 10th in that Category alone, so that I can know if I have spent too much BEFORE my checking account goes negative! :smile:

Thank you for your assistance. Please let me know if you need further....Because I hope this makes sense!
 
try:
=SUMIFS(Transactions!J:J,Transactions!A:A,">=" & $C$6,Transactions!A:A,"<=" & $D$6,Transactions!F:F,A15)
but I've had to guess that the dates are in Transactions!A:A
 
Unfortunately, it returned a value of $0.00. I changed the date column to Transactions!B:B, and I tried taking out the spaces that are present in your formula, but it did not report the correct number. I wonder if it is correctly identifying the Category listed in the A15 place? Before trying out the SUM-ming action, I input the formula on an item where there would be only one item to return, and deposit of income for that week.

The formula will be entered into the "October" sheet. I do not understand how the formula should flow, but the logical progression would be:
a. Find all transactions between the start date written in C6 and end date in D6 in the Date Column Transactions!B:B;
b. From those transactions, then match the Category name(s) listed in A15 on the Transactions!F:F column; and,
c. SUM those listed in Transactions!J:J to be reported in the October sheet.

So, ideally, as soon as I enter new transactions for the week, it should be added to the total in its Category on the October Sheet. I really appreciate your assistance!!!!!
 
Nevermind....I found what it was doing and fixed it. So the formula you supplied worked! AWESOME!
Just one more thing....how do i make it not show a negative value?
 
Ok, duh...wrap it all in ABS(). Sigh. I am sorry for the impulsive responding but I was doing this on my lunch break at work. However, when I went to save it, i am told it is not compatible with earlier versions of Excel. I did not think to include that my home computer is running 2003. Do you know how to remedy this?
 
It needs to be something like:
=SUMPRODUCT((Transactions!$B$2:$B$1000>=$C$6)*(Transactions!$B$2:$B$1000<=$D$6)*(Transactions!$F$2:$F$1000=A15),Transactions!$J$2:$J$1000)
Try not to use entire columns - it get's heavy on calculation - so you may have to adjust the 1000 in 4 places on the formula.
I'll leave you to add the ABS().
 
Last edited:
That did it!!!!

Thank you very much! I am a happy girl, and know where to visit for all my Excel hang-ups. :clap2:
 
You must log in or register to reply here.
Back
Top