General Betting
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
sort by:
Show
per page
Replies: 10
|
By:
Give me an example of the numbers and what exactly you want to count, and I'll have a quick go.
|
|
By:
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 |
|
By:
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 |
|
By:
http://www.brainbell.com/tutorials/ms-office/excel/Count_Only_One_Instance_Of_Each_Entry_In_A_List.htm explains how
|
|
By:
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 |
|
By:
=SUM(IF(FREQUENCY(i10:i1462,i10:i1462),1,0)) should count the number of unique 8 digit numbers.
|
|
By:
Thanks very much Bayes that worked perfectly!! Thanks also Ghetto Joe and Compound Magic!!
Cheers lads |
|
By:
Triggster 10 Mar 16:35
http://www.brainbell.com/tutorials/ms-office/excel/Count_Only_One_Instance_Of_Each_Entry_In_A_List.htm explains how Excellent site Triggster, never knew it exsisted, going of to study it in depth now. cheers |
|
By:
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 |
|
By:
cheers for that compound magic!!!
|