General Betting
Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
Can any1 help with the following
I have a column of number with over a 1000's cells, each cell containing a particular 8 digit number. My problem is I only want to count each digit once, but the digit could appear numerous times in the column.
What is the formula for counting how many individual numbers there is but if it appears say 3 times, its only to be counted once
Many thanks for any help
I have a column of number with over a 1000's cells, each cell containing a particular 8 digit number. My problem is I only want to count each digit once, but the digit could appear numerous times in the column.
What is the formula for counting how many individual numbers there is but if it appears say 3 times, its only to be counted once
Many thanks for any help
Show More
Give me an example of the numbers and what exactly you want to count, and I'll have a quick go.
Try
=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))
this must be entered as an array
change the cell references to match where your data is
=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))
this must be entered as an array
change the cell references to match where your data is
If it's just numbers you can use frequency
=SUM(IF(FREQUENCY(A:A,A:A)0,1))
assuming your values are in column A
=SUM(IF(FREQUENCY(A:A,A:A)0,1))
assuming your values are in column A
http://www.brainbell.com/tutorials/ms-office/excel/Count_Only_One_Instance_O... explains how
The numbers are in cell i10 to i1462.
number example = 100142627
Many Thanks.
I have being trying the formula's you have given and I am getting that circular reference error
number example = 100142627
Many Thanks.
I have being trying the formula's you have given and I am getting that circular reference error
=SUM(IF(FREQUENCY(i10:i1462,i10:i1462),1,0)) should count the number of unique 8 digit numbers.
Thanks very much Bayes that worked perfectly!! Thanks also Ghetto Joe and Compound Magic!!
Cheers lads
Cheers lads
Triggster 10 Mar 16:35
http://www.brainbell.com/tutorials/ms-office/excel/Count_Only_One_Instance_O... explains how
Excellent site Triggster, never knew it exsisted, going of to study it in depth now. cheers
http://www.brainbell.com/tutorials/ms-office/excel/Count_Only_One_Instance_O... explains how
Excellent site Triggster, never knew it exsisted, going of to study it in depth now. cheers
Just for future reference ~
=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))
the more than symbol did not show in front of the last 0 as typed
and should be inserted before the last 0
=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))
the more than symbol did not show in front of the last 0 as typed
and should be inserted before the last 0
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539