R
RJB
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!
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!