summing columns

  • Thread starter Thread starter drakey
  • Start date Start date
D

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!
 
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.
 
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)
 
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.
 
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.
 
Back
Top