summing columns

[drakey]

I have a worksheet that shows the spending from Mon-Fri for each week of the
year. So the worksheet is similiar to:

Mon Tues Wed Thurs Fri Mon Tues Wed etc.

On a seperate worksheet I want to total each, so I am currently using the
=sum(A1:E1). I am doing this for each week, so week two would be =sum(F1:J1).
I want to be able to just copy the formula across, instead of typing it over
and over, but when I try to copy, I end up with =sum(B1:F1). I know there
should be a way to do it.

Thanks!

 

[Brotherharry]

as long as you are writing the actual dates in your row instead of
just text for 'mon', 'tue', here's a solution which uses a couple of
steps to do what you want.

in A1 put this formula
=weeknum(A2) - this turns a date in to a week number
in A2 put your dates
in A3 put the amount of expenses

then on your other worksheet

in A1 put the number 3
in A2 put this formula
=SUMIF(Sheet1!1:1,A1,Sheet1!3:3)
this assumes your first sheet is called Sheet1, if it's not called
Sheet1, you'll need to change the text.
the sum if function looks for all the columns in row 1 on your first
sheet that have week number 3 in. It then adds up whatever is in row
3, i.e. your expenses

you should have the basics there to finish off.

 

[Paul]

I would try: SUM($A1:$E1)
The column letters should stay the same as you paste them over.

 

[Brotherharry]

Whilst it would keep letters the same, that won't work as OP is trying
to copy paste formulas by drag/auto-filling and he needs to the first
cell of the new range to increment more than 1 to pick up the first
day of his next week
i.e.
A1 on second sheet
=sum(Sheet1!A1:G1)
B1 on second cell
=sum(Sheet1!H1:N1)

 

[drakey]

I want the columns to change. So the first sum will be from columns A to F,
and then the next will be from columns G to L, but when I copy over, it
starts at B and goes to M. So how can I make it that when I copy over, it
will start at G and go to L.

 

[Brotherharry]

My stuff will do that. In the example I gave, highlight A1 and A2 then
drag to the left. You should end up with the number 4 in B1 and B2
will add up all the expenses for week 4.