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)

 

[Guest]

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]

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

 

[Guest]

Hi

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

Hope this helps.
Andy.

 

[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