Hi, this is probably simple to fix, but after googleing for a while, I
still haven't found it. I have a formula on a worksheet that includes a
sum of a range of data on another worksheet. When I sort the column of
data on the other worksheet, the sum changes on the other 'report'
worksheet as the range now contains different data. How can I get excel
to sum the specific cells in the original range (short of selecting
each cell with the ctrl key) and maintain focus on those cells, even
after a sort might disperse them all over the column? Thanks,

Rob


sorting range changes sum??? (excel)

Hi

Is there anything specific about those cells? Are they all the same month,
the same salesman, the same product? If so, you can sum using SUMIF or
SUMPRODUCT - whether they are sorted or not. It is very dodgy summing
information by where it is on the sheet!

Andy.

"bertbarndoor" wrote in message
oups.com...
Hi, this is probably simple to fix, but after googleing for a while, I
still haven't found it. I have a formula on a worksheet that includes a
sum of a range of data on another worksheet. When I sort the column of
data on the other worksheet, the sum changes on the other 'report'
worksheet as the range now contains different data. How can I get excel
to sum the specific cells in the original range (short of selecting
each cell with the ctrl key) and maintain focus on those cells, even
after a sort might disperse them all over the column? Thanks,

Rob


sorting range changes sum??? (excel)

Let's say I two worksheets in the workbook, a report worksheet which
rolls up data and such and a data sheet that has all the back-end data.
On the data sheet, I have 3 columns, last name, customer id, and sales
volume. Initially, I manually sort the data worksheet by last name. On
the seperate report worksheet, I sum the range of sales volume for
everyone whose last name begins with "B". However, if I go to the
"data" sheet and sort by, say sales volume, the total changes on the
"report" sheet as the original range that only included B last names
now contains all sorts of different cutomers????? Thanks,

Rob

Hi

I would suggest a SUMIF formula - like this:
=SUMIF(C2:C100,"B*",D2:D100)
where your names are in C2:C100 and your values are in D2:D100.

Hope this helps.
Andy.

"bertbarndoor" wrote in message
oups.com...
Let's say I two worksheets in the workbook, a report worksheet which
rolls up data and such and a data sheet that has all the back-end data.
On the data sheet, I have 3 columns, last name, customer id, and sales
volume. Initially, I manually sort the data worksheet by last name. On
the seperate report worksheet, I sum the range of sales volume for
everyone whose last name begins with "B". However, if I go to the
"data" sheet and sort by, say sales volume, the total changes on the
"report" sheet as the original range that only included B last names
now contains all sorts of different cutomers????? Thanks,

Rob

Thanks, it is not really what I was looking for, but it will do the
trick. I was just wondering if you could hard-code excel to focus on
specific cells and could get excel to follow those cells around during
a sort or rearrange, regardless of any state-dependency. Oh well, no
big, your solution works. Thanks again. _Rob