Weekly Totals [Closed]

Report
-
micoban
Posts
5
Registration date
Friday May 13, 2016
Status
Member
Last seen
May 13, 2016
-
Hello,

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?

16 replies

Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Well, unless you show us the format, we can only give you commands or functions.

Try =SUM(A1:A7)

Where column 1 through 7 are the days of the 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.
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Ok, so you still have not provided us with a reference. Use MY reference and use SUM!
What do you mean use your reference?
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Each and every "Cell" in a spreadsheet, has a reference. This reference has a letter, and a number.

SO, in other words, cell reference "A1", is the top most left handed cell in the spreadsheet.
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.
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Please post a picture of the sheet. When is this process supposed to happen? Whenever a total is entered?


I suggest you reformat your sheet for something like this:


It's kind of fun to do the impossible! -Walter Elias Disney
Posts
5
Registration date
Friday May 13, 2016
Status
Member
Last seen
May 13, 2016



I want the third column to do this on the last transaction of each week.
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
So is it a lot of work to work across the sheets with transactions, instead of down?

It's kind of fun to do the impossible! -Walter Elias Disney
Posts
5
Registration date
Friday May 13, 2016
Status
Member
Last seen
May 13, 2016


If I reformatted it like this, how could I make it work?
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Your day total already does it?

If you wanted to make slots for like 9 transaction in a day, then it would be:
=SUM(B2:B11)





It's kind of fun to do the impossible! -Walter Elias Disney
Posts
5
Registration date
Friday May 13, 2016
Status
Member
Last seen
May 13, 2016

I know that, but I want a weekly total
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Does this spreadsheet get cleared out every Week?
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
I am certain you could benefit from QUICKBOOKS!
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
.....you could then compare WEEKNUM for differences in each week date (that is another column).



ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Or, start with a template tab sheet with the core week, and start new weeks with new tabs, and rename them the week!
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
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:

https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

there is a whole library of functions available to you as an excel programmer!

If you do not have the time to program a custom spreadsheet, I encourage you to purchase QUICKBOOKS to handle this transactions.
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Oh, teachers don't want you to use quickbooks, huh?
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
Let me guess, you need this to complete a class?
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.
I hope that makes sense.
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
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!
Posts
5
Registration date
Friday May 13, 2016
Status
Member
Last seen
May 13, 2016

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.
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,642
we WILL learn from this......
Posts
5
Registration date
Friday May 13, 2016
Status
Member
Last seen
May 13, 2016

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.