MrExcel Publishing
Your One Stop for Excel Tips & Solutions

doing count on certain categories


Posted by ash on January 09, 2002 9:07 AM

I have a hundreds of rows with 5 columns in a worksheet. one column contains the name of different manufacturers . I would like to know how i can get a total count of each manufacturer.


Posted by IML on January 09, 2002 9:15 AM

You could use advance filter, unique entries to a different area to get a count.

Or use a fancy formula courtesy of Aladin
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))

adjust A1:A10 to suit your range. Do not however, default put in rows such as A:A, etc.

good luck

Posted by Aladin Akyurek on January 09, 2002 9:17 AM

Make a unique list of manufacturers in a separate column, say in F from F2 on (using e.g., Advanced Filter ).

Lets say that the names of manufacturers are in A.

In G2 enter: =COUNTIF(A:A,F2)

Doubble click on the little black square of G2 in order to copy down the formula as far as required.

Aladin

Posted by IML on January 09, 2002 9:20 AM

some day I will read the question asked, but not today


that formula provides you with the count of unique entries. See Aladin's post above for more detail on the filter and countif

sorry

Posted by lenze on January 09, 2002 2:41 PM

Pivot Table?(NT)