Use of COUNTIF

  • Thread starter Thread starter KiwiBrian
  • Start date Start date
K

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
 
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
 
Thanks VERY much Julie.
A superb result.
Brian

JulieD said:
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
Click to expand...
 
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
 
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
 
Back
Top