Prev: Mask Account Number
Next: Repetitive macros
From: RJB on 6 Apr 2010 17:56
The RANGE and CRITERIA for my COUNTIF are on two separate tabs of the same
file.

When I set it up, nice.

But if I sort any other field in that column, the CRITERIA refers back to
its ORIGINAL position, and returns in its new cell the count of the data that
occupies the ORIGINAL position.

In other words:

So I have:

A2=COUNTIF(Sheet2!'Range',Sheet1!B2) | B2="Candy"
A3=COUNTIF(Sheet2!'Range',Sheet1!B3) | B3="Dreadlocks"
A4=COUNTIF(Sheet2!'Range',Sheet1!B4) | B4="Bris"
A5=COUNTIF(Sheet2!'Range',Sheet1!B5) | B5="Armistice Day"

Or:
38 Candy
79 Dreadlocks
29 Bris
173 Armistice Day



If I do an alpha sort on column B, I get:


A2=COUNTIF(Sheet2!'Range',Sheet1!B5) | B2="Armistice Day"
A3=COUNTIF(Sheet2!'Range',Sheet1!B4) | B3="Bris"
A4=COUNTIF(Sheet2!'Range',Sheet1!B2) | B4="Candy"
A5=COUNTIF(Sheet2!'Range',Sheet1!B3) | B5="Dreadlocks"

Or:
79 Armistice Day
38 Bris
173 Candy
29 Dreadlocks

See? The first row says, Hey, the criteria for this USED to be in cell B5.
So instead of counting the number of times "Armistice Day" appears in the
sheet, I'll look down and see what's in B5 now - "Dreadlocks". Alright, I'll
count THAT and return that value.

What I'd like is for it to always return the count of the cell next to it.
Because I'd like to sort by the COUNTIF, but I'd also like to sort by other
criteria in the same table.

Help!
 | 
Pages: 1
Prev: Mask Account Number
Next: Repetitive macros