Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Sorting and COUNTIF
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
RJB RJB is offline
external usenet poster
  
Posts: 86
Default Sorting and COUNTIF
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!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,856
Default Sorting and COUNTIF
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 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!

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Sorting and COUNTIF
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 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 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!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,203
Default Sorting and COUNTIF
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!
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Sorting and COUNTIF
On Apr 6, 10:18*pm, JLatham 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.
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,203
Default Sorting and COUNTIF
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 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.
.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Sorting and COUNTIF
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.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF, Sorting, on Two Sheets RJB Excel Discussion (Misc queries) 1 April 7th 10 05:08 AM
Countif, Sorting = Two Sheets RJB Excel Discussion (Misc queries) 0 April 7th 10 12:13 AM
COUNTIF, Sorting, Two Sheets RJB Excel Discussion (Misc queries) 0 April 6th 10 10:56 PM
Automatic sorting (giving max and min) based on custom sorting lis Joe Lewis[_2_] Excel Worksheet Functions 4 November 23rd 08 05:12 AM
COUNTIF or not to COUNTIF on a range in another sheet Ellie Excel Worksheet Functions 4 September 15th 05 10:06 PM


All times are GMT +1. The time now is 01:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement