Count

[Guest]

I have a list of codes in a column, each code will be appearing many times. I
need to count the number of codes - how do I do it.

eg:
A100
B100
A100
A100
B100

Number of codes in the above list are 2.

Thanks

 

[squenson via OfficeKB.com]

It depends.

If you need a formula, then you have to write a macro, as far as I know.

If you want a one time count, then create a pivot table with just this column,
then count the number of items. If you can sort the list, in a new column use
a formula like IF(A2=A1,0,1), then make the sum of this column.

 

[Guest]

Sena, Try this

=SUM(1/COUNTIF(A1:A10,A1:A10))

Adjust to the size of your range then enter it with Ctrl+Shift+Enter

Mike

 

[Guest]

I'd say your best bet would be a Pivot Table Report.

Highlight your list, and goto Data - Pivot Table Report. You may need use
the OPTIONS button to place your data in the correct place (i.e. drag it once
in the "data" central area, and then again in the "colums" area - that way it
will make colums out of your rows, but will group together similar codes and
count them for you.

Lemme know if it helps.

 

[Guest]

Wonderful, it works ... Thanks !
--
Cheers !

Sena, Try this

=SUM(1/COUNTIF(A1:A10,A1:A10))

Adjust to the size of your range then enter it with Ctrl+Shift+Enter

Mike

 

[Guest]

That's excellent news and thanks for the feedback.

 

[Guest]

Hi,

Do the following:

1. Select a single cell on the top of the range.
2. On the Data menu, point to Filter, and then click the AutoFilter.
3. Click the arrow next to the cell and select the cell value to want to view.
4. Hightlight the value and you will see the Count value of the number of
accurances in that cell value.

Note: Right-click on the status bar and select Count before filtering the
range.

Challa Prabhu