I have a column of dates and a column of transactions. Often there are multiple transactions on the same day. How can I enter a formula in another column that shows the total of transactions per week?
The data looks something like this:
DATE TRANSACTION WEEK TOTAL
5/11/16 $12.00
5/11/16 ($2.00)
5/16/16 $16.00
5/29/16 $8.00
5/30/16 ($3.00)
6/12/16 $10.00
6/13/16 ($100.00)
I want the third column to calculate the total of each week.
Okay,
Column A has the dates. A2 & A3 both contain the same date, Wednesday of this week, A4 is the following Monday. Column B contains the corresponding transactions on each day. One day can have more than one transaction, but not every day has a transaction. I want column C to calculate the total transactions for each week and do so automatically. So cell C3 would show $10.00 and cell C4 would show $16.00 and cell C6 would show $5.00 and cell C8 would show ($90.00).
The only data entered manually are the dates and the transaction amounts.
There is no way to automate the spreadsheet. It is very tough to make a dynamically sized spreadsheet programmatically, and trap all possible errors. I encourage you to take a look here:
No this is not for a class. I'm trying to set up a personal budget. I've tried playing around with WEEKNUM and IF statements, but have had no luck. I've tried the DAYS function, but since it's constantly changing, there is no real end date. That is why I added the "DAY TOTAL" column. My prior attempt was =IF(WEEKNUM(A2,1)=WEEKNUM(A3,1)," ",SUM(D2)). But as the formula moves down the column I don't know how to make it go back to the first day of the week to SUM the total.
Hang tight! There are excel guys that will get you out of complex issues...I am just a low level simple type of guy! there is always a simple fix without making it too complex!
I appreciate your input. I've taken several classes on excel and this is my toughest problem yet, and it's not even for a grade, haha. This is what I have so far. Maybe we can both learn from this.
This formula is sooo close. It changes exactly when I need it to, but it doesn't add the previous rows. I get ($20.19) in cell D7 and $15.00 in cell D8. The rest of column D is blank like it should be.