# Weekly Totals

Closed
micoban - May 11, 2016 at 05:19 PM
micoban Posts 5 Registration date Friday May 13, 2016 Status Member Last seen May 13, 2016 - May 13, 2016 at 10:48 PM
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?

Related:

## 16 responses

Blocked Profile
May 11, 2016 at 05:36 PM
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.
Blocked Profile
May 11, 2016 at 08:03 PM
Ok, so you still have not provided us with a reference. Use MY reference and use SUM!
What do you mean use your reference?
Blocked Profile
May 12, 2016 at 04:23 PM
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.

Didn't find the answer you are looking for?

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.
Blocked Profile
May 13, 2016 at 02:43 PM
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
micoban Posts 5 Registration date Friday May 13, 2016 Status Member Last seen May 13, 2016
May 13, 2016 at 03:19 PM

I want the third column to do this on the last transaction of each week.
Blocked Profile
May 13, 2016 at 03:21 PM
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
micoban Posts 5 Registration date Friday May 13, 2016 Status Member Last seen May 13, 2016
May 13, 2016 at 04:05 PM

If I reformatted it like this, how could I make it work?
Blocked Profile
May 13, 2016 at 05:00 PM

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
micoban Posts 5 Registration date Friday May 13, 2016 Status Member Last seen May 13, 2016
May 13, 2016 at 05:03 PM
I know that, but I want a weekly total
Blocked Profile
May 13, 2016 at 07:09 PM
Does this spreadsheet get cleared out every Week?
Blocked Profile
May 13, 2016 at 07:09 PM
I am certain you could benefit from QUICKBOOKS!
Blocked Profile
May 13, 2016 at 07:11 PM
.....you could then compare WEEKNUM for differences in each week date (that is another column).

Blocked Profile
May 13, 2016 at 07:14 PM
Or, start with a template tab sheet with the core week, and start new weeks with new tabs, and rename them the week!
Blocked Profile
May 13, 2016 at 07:23 PM
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:

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.
Blocked Profile
May 13, 2016 at 07:26 PM
Oh, teachers don't want you to use quickbooks, huh?
Blocked Profile
May 13, 2016 at 07:27 PM
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.
Blocked Profile
May 13, 2016 at 10:28 PM
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!
micoban Posts 5 Registration date Friday May 13, 2016 Status Member Last seen May 13, 2016
May 13, 2016 at 10:35 PM
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.
Blocked Profile
May 13, 2016 at 10:39 PM
we WILL learn from this......
micoban Posts 5 Registration date Friday May 13, 2016 Status Member Last seen May 13, 2016
May 13, 2016 at 10:48 PM
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.