sorting range changes sum??? (excel)

[bertbarndoor]

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" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

[bertbarndoor]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[bertbarndoor]

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