How to count frequency of items in the table (given 2 columns in a table)

**xianwinwin** · 06-21-2010, 06:48 PM

Hi all,

This one is really bugging me. I have a list of 3000 items. The list has 2 columns: ID, STATUS

Please Login or Register  to view this content.

What I wish to do is to COUNT the frequency of the IDs. For example, ID 13 appears 6 times and ID 45 4 times). But when the list is so long it's really hard to SUM every ID.

Thank you for any pointers.

**Ganesh7299** · 06-21-2010, 07:12 PM

you can use COUNTIF formula.

=COUNTIF(Range,Criteria)

e.g. =COUNTIF(Col ID,13)

**pike** · 06-21-2010, 07:23 PM

Hi xianwinwin
you could use something like this from zbor...

'{=IF(ISERROR(INDEX(A30:A58,SMALL(IF(IF(ISERROR(MATCH(A30:A58,A30:A58,0)),",MATCH(A30:A58,A30:A58,0))<>ROW(INDIRECT(1:"&ROWS(30:58))),"",IF(ISERROR(MATCH(A30:A58,A30:A58,0)),"",MATCH(A30:A58,A30:A58,0))),ROW(INDIRECT("1:"&ROWS(30:58)))))),"",INDEX(A30:A58,SMALL(IF(IF(ISERROR(MATCH(A30:A58,A30:A58,0)),"",MATCH(A30:A58,A30:A58,0))<>ROW(INDIRECT("1:"&ROWS(30:58))),"",IF(ISERROR(MATCH(A30:A58,A30:A58,0)),"",MATCH(A30:A58,A30:A58,0))),ROW(INDIRECT("1:"&ROWS(30:58))))))}
to find all the unquie values and

=SUMPRODUCT(--(A1:A13=A4))
to count them

**martindwilson** · 06-21-2010, 07:42 PM

pike i don't know what that's supposed to do but its got errors in it!
if you wanted to list unique with a function try
=INDEX($A$1:$A$350,MATCH(1,INDEX(($A$1:$A$350<>"")*ISNA(MATCH($A$1:$A$350,$C$1:C1,0)),0),0))
with list in a1 down put this array formula in c2
but this has no bearing on the op's question! which i think Ganesh7299 answered

**xianwinwin** · 06-21-2010, 08:52 PM

Thank you all.
The proposed solution of Ganesh7299 will not work. Because I will have to write this method X time (X=number of unique IDs) I have something like ~150 unique ids so it will not work.

I wish to write once and drag it down and get the result next to the unique ID. example:

look at the imagehttp://img341.imageshack.us/img341/7...00621at851.png

the result is on the left side.

**martindwilson** · 06-22-2010, 03:17 AM

to get similar to that but the result will be next to the last instance not the first drag this down
=IF(COUNTIF($b$2:b2,b2)=COUNTIF(b:b,b2),COUNTIF($b$2:b2,b2),"")