Countif Formula /Sort Bug???

[Guest]

Anyone seen this before, and if so, is it a bug?

using a simple countif, entries in Column A are:A
Data
a
a
a
b
b
b
b
(So, a heading of Data followed by 3 a's and 4 b's)

on the same worksheet i put
B ¦C
Criteria¦Count
a ¦=countif(A:A, B2)
b ¦=countif(A:A,B3)

which shows
a 3
b 4 as expected.

Now do a descending sort on columns b and c by column c (on the countif) and
you get
b 4
a 3 as expected.

However, if the criteria and countif columns are on a separate worksheet,
doing the sort gives this results
b 3
a 4

which is wrong!

the reason being that the Countif formula that was originally looking at
cell a2 is still looking at a2 after the sort, it no longer simply looks "one
cell the left"

Is it expected behavour in Excel that the formula adjustments made when
sorting are different depending on the location of the target cells?
Thanks!

 

[Bob Phillips]

Are you sure you did that correctly, it shows 4 and 3 as expected for me.
What do your formulae on the other sheet look like?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Sheet 2 started off as:
A¦ B
Data¦Counts
a¦Countif(Sheet1!A:A,Sheet2!A2) (counts the As in column A of sheet1)
b¦Countif(Sheet1!A:A,Sheet2!A3) (Counts the Bs in column B of sheet1)

Then I sort columns A and B descending by column B, with a header

This results in:
A¦ B
Data¦Counts
B¦Countif(Sheet1!A:A,Sheet2!A3)
A¦Countif(Sheet1!A:A,Sheet2!A2)

So the "B" row is actually counting A's after the sort and the "A" row is
counting B's