Use of COUNTIF

[KiwiBrian]

I wish to know how many instances of a numeral are in a column.
I am using the following formula:-
COUNTIF($A$2:$A$101,1) to give a count of every occurance of 1 in the 100
row list in Column A.
If I want a count of all unique numerals in the column I can repeat the
above formula with the last numeral different in every example.
A bit impractical with a lot of numerals to check for.
Is there a way I can change the formula so that I can just enter it once and
drag it down the column to row 101?
Or an alternative way of tackling the problem?
I hope I have described this clearly.
Thanks
Brian Tozer

 

[JulieD]

Hi Brian

if you type 1 in say Cell C2
hold down the ctrl & drag down to 101
then put the COUNTIF formula in D2
as
=COUNTIF($A$2:$A$101,C2)
and double click the fill handle

this should give you what you're after - let us know how you go

Cheers
JulieD

 

[KiwiBrian]

Thanks VERY much Julie.
A superb result.
Brian

Hi Brian

if you type 1 in say Cell C2
hold down the ctrl & drag down to 101
then put the COUNTIF formula in D2
as
=COUNTIF($A$2:$A$101,C2)
and double click the fill handle

this should give you what you're after - let us know how you go

Cheers
JulieD

 

[Gord Dibben]

Brian

See my answer to your other post using the ROW() function.

Stick to one thread when needing answers to same question, please.

Gord Dibben Excel MVP

 

[JulieD]

glad to assist - thanks for the feedback

Thanks VERY much Julie.
A superb result.
Brian

 

[KiwiBrian]

Thanks Gord very much for your most interesting and illuminating response
which my ISP lost due to a local problem.
However seeing your followup below I went and found it on Google.
I have tested it and it works fine.
Now to disect it and analyze it and learn from it.
Thanks again
Brian