software

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

using a simple countif, entries in Column A aA
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 quot;one
cell the leftquot;

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

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)

quot;orcfodderquot; gt; wrote in message
news
gt; Anyone seen this before, and if so, is it a bug?
gt;
gt; using a simple countif, entries in Column A aA
gt; Data
gt; a
gt; a
gt; a
gt; b
gt; b
gt; b
gt; b
gt; (So, a heading of Data followed by 3 a's and 4 b's)
gt;
gt; on the same worksheet i put
gt; B ¦C
gt; Criteria¦Count
gt; a ¦=countif(A:A, B2)
gt; b ¦=countif(A:A,B3)
gt;
gt; which shows
gt; a 3
gt; b 4 as expected.
gt;
gt; Now do a descending sort on columns b and c by column c (on the countif)
and
gt; you get
gt; b 4
gt; a 3 as expected.
gt;
gt; However, if the criteria and countif columns are on a separate worksheet,
gt; doing the sort gives this results
gt; b 3
gt; a 4
gt;
gt; which is wrong!
gt;
gt; the reason being that the Countif formula that was originally looking at
gt; cell a2 is still looking at a2 after the sort, it no longer simply looks
quot;one
gt; cell the leftquot;
gt;
gt; Is it expected behavour in Excel that the formula adjustments made when
gt; sorting are different depending on the location of the target cells?
gt; Thanks!
gt;
gt;
gt;

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 quot;Bquot; row is actually counting A's after the sort and the quot;Aquot; row is
counting B's

quot;Bob Phillipsquot; wrote:

gt; Are you sure you did that correctly, it shows 4 and 3 as expected for me.
gt; What do your formulae on the other sheet look like?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;orcfodderquot; gt; wrote in message
gt; news
gt; gt; Anyone seen this before, and if so, is it a bug?
gt; gt;
gt; gt; using a simple countif, entries in Column A aA
gt; gt; Data
gt; gt; a
gt; gt; a
gt; gt; a
gt; gt; b
gt; gt; b
gt; gt; b
gt; gt; b
gt; gt; (So, a heading of Data followed by 3 a's and 4 b's)
gt; gt;
gt; gt; on the same worksheet i put
gt; gt; B ¦C
gt; gt; Criteria¦Count
gt; gt; a ¦=countif(A:A, B2)
gt; gt; b ¦=countif(A:A,B3)
gt; gt;
gt; gt; which shows
gt; gt; a 3
gt; gt; b 4 as expected.
gt; gt;
gt; gt; Now do a descending sort on columns b and c by column c (on the countif)
gt; and
gt; gt; you get
gt; gt; b 4
gt; gt; a 3 as expected.
gt; gt;
gt; gt; However, if the criteria and countif columns are on a separate worksheet,
gt; gt; doing the sort gives this results
gt; gt; b 3
gt; gt; a 4
gt; gt;
gt; gt; which is wrong!
gt; gt;
gt; gt; the reason being that the Countif formula that was originally looking at
gt; gt; cell a2 is still looking at a2 after the sort, it no longer simply looks
gt; quot;one
gt; gt; cell the leftquot;
gt; gt;
gt; gt; Is it expected behavour in Excel that the formula adjustments made when
gt; gt; sorting are different depending on the location of the target cells?
gt; gt; Thanks!
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;