COUNTIF, Sorting, Two Sheets

R

RJB

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top