From: RJB on 6 Apr 2010 19:13 This may be a double post; apologies. This problem seems to happen only when my RANGE is on a different worksheet than my CRITERIA. When I run a COUNTIF, if I sort the table, the criteria returns the count of whatever is in the position of where the CURRENT criteria USED to be. I don't want that. I never know how to explain relative and absolute references, so let me give you an example. Sheet 2 has an inventory of four items: Hard-boiled eggs, oil filters, Jonas Brothers posters, and dry-erase markers. For simplicity, let's call it "Sheet 2! Range". I do a physical count of my inventory and I know I have 12 eggs, 9 filters, 23 posters, and 15 dry-erase markers. On Sheet 1, I list my items in column A: A2=Jonas Bros. posters A3=Dry-Erase markers A4=Hard-boiled eggs A5=Oil filters Column B has my count formula: B2=COUNTIF(Sheet2!Range,Sheet1!A2) B3=COUNTIF(Sheet2!Range,Sheet1!A3) B4=COUNTIF(Sheet2!Range,Sheet1!A4) B5=COUNTIF(Sheet2!Range,Sheet1!A5) So this returns: Jonas Bros. Posters | 23 Dry-Erase Markers | 15 Hard-Boiled Eggs | 12 Oil filters | 9 OK so far. But what if I want to alphabetize my list? So when I sort, I get: Dry-Erase Markers | 12 Hard-Boiled Eggs | 23 Jonas Bros. Posters | 15 Oil filters | 9 What the what? What it's done is, B2 now says, "Hey, A2 has 'Dry Erase Markers'. That USED to be in A3. So I'll count what's in A3 - which happens to be 'Eggs'. So I will count the quantity of the value in 3 and return it in 2." And so on So now my data looks like this: A2=Dry-Erase Markers A3=Hard-Boiled Eggs A4=Jonas Bros. Posters A5=Oil filters B2=COUNTIF(Sheet2!Range,Sheet1!A3) B3=COUNTIF(Sheet2!Range,Sheet1!A4) B4=COUNTIF(Sheet2!Range,Sheet1!A2) B5=COUNTIF(Sheet2!Range,Sheet1!A5) I need to be able to sort AND count the values. What to do what to do? Thanks!  |  Pages: 1 Prev: Repetitive macrosNext: Problems with merged cells