Excel 2007 : Countif producing zero

Hi

I am using =COUNTIF($A$2:$A$80000,$C$2:$C$80000).
I have removed duplicates from c and it works great.

I then extended the range to A and B ... C and D =COUNTIF($A$2:$B$80000,$B$2:$C$80000)
I get a zero.

Hope this can be done.

All the best.
Mark

Hi TMShucks
I have tried to do as you suggested but still got Zero. Your suggestion gives an overlap. But even if I move it across I still get zero.

I have a list of stores and their categories . I want to have a list where I can see the stores and their categories with this frequency .
If I use =COUNTIF($A$2:$A$82023,$C$2:$C$82023) The result is the number of times the store is featured. And this is fine for the first answer to my needs.
I want to further define the information by also giving the stores with the category adjacent and for the number not to be the same all the time as it is if I use the above and then extend the sorting to include the category.

I have found a solution for this. It is a little “Heath Robinson” so I have not marked it as solved. Moderators please feel free to do as you see fit.

I used =A&D&B to create a new entry in C. There was data in A and B. D was two spaces to make the result readable.
I copied the result in C to itself using paste special and values.
Then copied C to D.
Then removed duplicates in D.
I then used the =COUNTIF($B$2:$B$85000,$C$2:$C$85000) to give a result in E. This takes a long time with my set up though I have what I am told should be a fast machine.
Next I copy the result to itself (special paste and values).
Then sort C, D and E by E using largest to smallest.
This gives me the result I wanted with the categories in the original B not being mixed up.

Hope this might be useful to someone.

If there is a faster method then I would love to learn it so please post

All the Best
Mark

realized that if I remove all the commas an then replace the double spaces with a comma I can get the correct answer. If I then copy it to a text file and reopen it in Excel from the text I can end up with what I want with the categories in separate columns which is preferable to one column.

You can see why I am hoping there is a formula to get round this. Either way I need to have my answers in the morning so that I can get the last bit of this done on Monday.

Hope this does not give you a head ake on this Bank Holiday weekend!!!!!!!!!!!!!!

Hi
Yes I see I entered it incorrectly Sorry. N=But not in the excel sheet as I can move it about.
This Is a follow on from a post I popped up yesterday http://www.excelforum.com/excel-2007...rge-sheet.html
I have taken out two of the columns.
They are stores and a category.
I want to end up with the frequency of the stores see frequency of stores as a number.
In some instances the stores have more than one category and I also want to show them separately. I have in fact made them almost all the same as being different is not critical to this project.
Column A and B come from the main sheet.
First I copied A and B to C and D to do the frequency run as discussed earlier just on A and C. This gives me what you see in D which is a list of the stores only.
I now wanted to be able to get the same with the store and the category as one item. IE a Wal-mart supermarket would not be the same as a Wal-Mart Connivance store. Using the first count the entry will go in twice, each entry having the total amount.
Wal-Mart Connivance store 25
Wal-mart supermarket 25
When in fact there are 20 supermarkets and 5 stores
I needed to sort by both columns as tough they were one. I removed all the commas and then using the “=” I made column E which I then copied etc to create a frequency list as you see. This was then copied into a text and then re-imported into Excel with the comma as the separator. The result was is what you see in H and I.

This shows you what I wanted to get.
This relates back to my post of yesterday only then I was hoping that there was a function or mixture of them which would do all this in one go. I am not the best at this all I know is what I want to achieve.
Also I will be moving all this to Access next where these operations are easier. There is still a lot of cleaning of the data first. In the meantime I want to get this part finished.

I hope this makes sense. One of the reasons I would like to get a single formula apart from the speed is because I often forget where I have gotten to and suddenly have loads of excel sheets open.

All the best
Mark.

Hi TMShucks

I do not know where to find the attached example. Should it be just below or somewhere else?
Mark

Just got to this. Now a couple of days later and I had done the operation manually. This is fab. Thanks. There is so much here I am not sure the title for the query is anything like sufficient. Again thanks for all this great stuff.
mark.

I am going to attempt to use this now to do the next operation as I want to show the number of stores in a particular state with a particular category.