Excel 2007 : selective counting of repeated elements

**buhnen** · 06-19-2010, 06:19 PM

Hello everybody!

I need your help about the following issue. I need to perform an operation as you can figure out if you follow from the attached screen capture.

Within a column I have to count how many cells there are belonging to one class ( a class means here the same kind of value), keeping in mind that there are several kinds of classes.

It seems a bit complex to me and I'm not even sure about if this is to be solved either with functions or macro programming (which I don't know about).

Could you please help me with this?
Thanks in advance

**ConneXionLost** · 06-19-2010, 07:31 PM

If your criteria is in column "I" as per your picture, then try:

Please Login or Register  to view this content.

Cheers,

**buhnen** · 06-21-2010, 08:45 PM

mmmm...nope, it hasn't worked .It only solves part of the problem...The variety of values displayed in the cells is so large that it doesn't worth to use the "count if" function and define the different criteria values that should be previously and manually looked up within the column.

The type of solution I'm searching for already implies that the searching and identification of those different values is performed by the function itself, and not by the user.

Imagine there might be around 100 different values within the column. In this case the user would have to define 100 times the criteria to match the count if operation. There has to be a way that Excel compare cells among them and returns all the set of values.

For instance:

"N9" appears 10 times
"L9" appreas 3 times
...
"RA01.1" appears one time

without having to define "N9", "L9" and "RA01.1" like criteria search manually. Excel should already detect they are different values and count how many times they do appear.

**Skidwiz** · 06-21-2010, 09:00 PM

I believe that what you are asking for is for excel to recognize that there are exactly 100 (guess) different designations in Column A, and to have excel return each designation with how many times that particular designation occurs within the column. If this is the case, what you are asking for is only possible through VBA coding.

**ConneXionLost** · 06-22-2010, 01:50 AM

Without knowing what you want the result to look like, this just devolves into a guessing game. But hey, fun can be found in many things. In the meantime change my formula from:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

and autofill it down 1804 rows.

Cheers,

**buhnen** · 06-22-2010, 12:38 PM

Thanks Skid and Connection for your quick answers

Yes, Skid is right: That is exactly what excel should do ....I hope someone else with more expertise on VBA programming could lend me a hand about this

**teylyn** · 06-23-2010, 05:14 PM

Buhnen,

feel free to start a new thread in the Programming forum. This way you'll get more attention, because of lower answer counts. Refer and link to this thread here, so people see your are not double posting.

cheers,