Sorting and COUNTIF
in [General]
|
From: RJB on 6 Apr 2010 20:19 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!
From: Pete_UK on 6 Apr 2010 20:33 I can see that you have posted 4 times to this group - please stop !! Sort the values in column A (only), rather than including the formula column (B) in your sort range. Hope this helps. Pete On Apr 7, 1:19 am, RJB <R...(a)discussions.microsoft.com> 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!
From: RJB on 6 Apr 2010 23:20 OK, well I started with a "This may have double posted" apology, so, you know, you didn't have to lead with the rebuke... CLEARLY something wasn't working correctly, or I wouldn't have said it, you know? Anyway: How can you sort PART of a table without sorting the REST of the table? If I read you right, you're saying to only sort column A. But I need the functionality to be able to sort on B (and C, D, and E). Thanks On Apr 6, 6:33 pm, Pete_UK <pashu...(a)auditel.net> wrote: > I can see that you have posted 4 times to this group - please stop !! > > Sort the values in column A (only), rather than including the formula > column (B) in your sort range. > > Hope this helps. > > Pete > > On Apr 7, 1:19 am, RJB <R...(a)discussions.microsoft.com> 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!
From: JLatham on 7 Apr 2010 00:18 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!
From: RJB on 7 Apr 2010 01:26
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. |