Sorting and COUNTIF
in [General]
|
From: JLatham on 7 Apr 2010 02:56 Excel seems to think that if there's a sheet reference, even to the current sheet, that it should keep the cell references as they were, treating them as absolute for situations like this. "RJB" wrote: > On Apr 6, 10:18 pm, JLatham <JLat...(a)discussions.microsoft.com> wrote: > > The other response, from Pete_UK will work, alternatively you can remove the > > sheet reference to the criteria in your formula: > > B2=COUNTIF(Sheet2!Range,A2) > > B3=COUNTIF(Sheet2!Range,A3) > > B4=COUNTIF(Sheet2!Range,A4) > > B5=COUNTIF(Sheet2!Range,A5) > > > > and sort by both columns and it'll still work. > > > > "RJB" wrote: > > > 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! > > I still don't understand Pete's solution, but yours worked fine. The > only reason why Sheet1! was in there is because XL "forced" it on me; > I can't even fathom why that made a difference, but it did. > > Thank you. > . >
From: RJB on 7 Apr 2010 10:32
That I would have understood, but it does a double-shuffle, which is what I don't get. I guess it doesn't matter why, I have a solution. |