sorting issues

[michael]

I am adding up rows 1, 4, & 10 in column B to equal 15.

A B
v 1
s 2
h 3
z 4
y 5
a 6
b 7
p 8
o 9
i 10

15

Now I want to sort by column A and this is what I get.
A B
a 6
b 7
h 3
i 10
o 9
p 8
s 2
v 1
y 5
z 4

20

I don't have absolute signs in my sum formula, but i
can't figure out why my cells are moving and not keeping
my total of 15.

Please help. Thanks

 

[Frank Kabel]

Hi
this result is correct. You sum formula does NOT change the cell
references if you sort. So it still add B1+B4+B10 (which is 20 in your
second example)

 

[RagDyer]

Would you care to use something like this instead:

=SUMPRODUCT((A1:A10={"v","z","i"})*B1:B10)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I am adding up rows 1, 4, & 10 in column B to equal 15.

A B
v 1
s 2
h 3
z 4
y 5
a 6
b 7
p 8
o 9
i 10

15

Now I want to sort by column A and this is what I get.
A B
a 6
b 7
h 3
i 10
o 9
p 8
s 2
v 1
y 5
z 4

20

I don't have absolute signs in my sum formula, but i
can't figure out why my cells are moving and not keeping
my total of 15.

Please help. Thanks

 

[michael]

I was just trying to keep it simple. I thought as long
as the absolute symbol wasn't used, the cell would move
with the sort.

Thanks for the help