Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
Hi,
I have a col contains numbers and the col also have duplicates. Now i want to count how many duplicates against 1 value. This i can do with countif easily but the important point is if the 1st value has duplicates then it shud say the count in the 1st value and all other duplicate values shud be blank. pls. refer the below example for better understanding, Values Count Remarks 100 3 (Count of 100 in the col is 3) 101 2 (Count of 101 in the col is 2) 100 (100 is duplicate so the count shud be blank) 100 (100 is duplicate so the count shud be blank) 101 (100 is duplicate so the count shud be blank) 102 2 (Count of 102 in the col is 2) 103 1 (Count of 103 in the col is 1) 102 (102 is duplicate so the count shud be blank) 555 1 (555 there is no duplicates in the col and the count is 1) I hope the above example helps to understand better. I request any one of you to help me in this requirement. Thanks in Advance -Chr |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
Data in A1 down
In B1: =IF(COUNTIF(A$1:A1,A1)1,"",COUNTIF(A:A,A1)) Copy down to compute exactly as indicated Pl remember to press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Christopher Naveen" wrote: I have a col contains numbers and the col also have duplicates. Now i want to count how many duplicates against 1 value. This i can do with countif easily but the important point is if the 1st value has duplicates then it shud say the count in the 1st value and all other duplicate values shud be blank. pls. refer the below example for better understanding, Values Count Remarks 100 3 (Count of 100 in the col is 3) 101 2 (Count of 101 in the col is 2) 100 (100 is duplicate so the count shud be blank) 100 (100 is duplicate so the count shud be blank) 101 (100 is duplicate so the count shud be blank) 102 2 (Count of 102 in the col is 2) 103 1 (Count of 103 in the col is 1) 102 (102 is duplicate so the count shud be blank) 555 1 (555 there is no duplicates in the col and the count is 1) I hope the above example helps to understand better. I request any one of you to help me in this requirement. Thanks in Advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
B2: =IF(COUNTIF($A$1:A1,A2),"",COUNTIF(A:A,A2))
copy down -- __________________________________ HTH Bob "Christopher Naveen" wrote in message ... Hi, I have a col contains numbers and the col also have duplicates. Now i want to count how many duplicates against 1 value. This i can do with countif easily but the important point is if the 1st value has duplicates then it shud say the count in the 1st value and all other duplicate values shud be blank. pls. refer the below example for better understanding, Values Count Remarks 100 3 (Count of 100 in the col is 3) 101 2 (Count of 101 in the col is 2) 100 (100 is duplicate so the count shud be blank) 100 (100 is duplicate so the count shud be blank) 101 (100 is duplicate so the count shud be blank) 102 2 (Count of 102 in the col is 2) 103 1 (Count of 103 in the col is 1) 102 (102 is duplicate so the count shud be blank) 555 1 (555 there is no duplicates in the col and the count is 1) I hope the above example helps to understand better. I request any one of you to help me in this requirement. Thanks in Advance -Chr |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
Try this in B2:
=IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),"") and copy this down. Hope this helps. Pete On Jan 19, 9:34*am, Christopher Naveen wrote: Hi, I have a col contains numbers and the col also have duplicates. Now i want to count how many duplicates against 1 value. This i can do with countif easily but the important point is if the 1st value has duplicates then it shud say the count in the 1st value and all other duplicate values shud be blank. pls. refer the below example for better understanding, Values *Count * *Remarks 100 * * * * 3 * * * *(Count of 100 in the col is 3) 101 * * * * 2 * * * *(Count of 101 in the col is 2) 100 * * * * * * * * * (100 is duplicate so the count shud be blank) 100 * * * * * * * * * (100 is duplicate so the count shud be blank) 101 * * * * * * * * * (100 is duplicate so the count shud be blank) 102 * * * * 2 * * * *(Count of 102 in the col is 2) 103 * * * * 1 * * * *(Count of 103 in the col is 1) 102 * * * * * * * * * (102 is duplicate so the count shud be blank) 555 * * * * 1 * * * *(555 there is no duplicates in the col and the count is 1) I hope the above example helps to understand better. I request any one of you to help me in this requirement. Thanks in Advance -Chr |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
Thank u so much max. it works fine.
"Max" wrote: Data in A1 down In B1: =IF(COUNTIF(A$1:A1,A1)1,"",COUNTIF(A:A,A1)) Copy down to compute exactly as indicated Pl remember to press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Christopher Naveen" wrote: I have a col contains numbers and the col also have duplicates. Now i want to count how many duplicates against 1 value. This i can do with countif easily but the important point is if the 1st value has duplicates then it shud say the count in the 1st value and all other duplicate values shud be blank. pls. refer the below example for better understanding, Values Count Remarks 100 3 (Count of 100 in the col is 3) 101 2 (Count of 101 in the col is 2) 100 (100 is duplicate so the count shud be blank) 100 (100 is duplicate so the count shud be blank) 101 (100 is duplicate so the count shud be blank) 102 2 (Count of 102 in the col is 2) 103 1 (Count of 103 in the col is 1) 102 (102 is duplicate so the count shud be blank) 555 1 (555 there is no duplicates in the col and the count is 1) I hope the above example helps to understand better. I request any one of you to help me in this requirement. Thanks in Advance |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
Thank u so much bob. it works fine.
"Bob Phillips" wrote: B2: =IF(COUNTIF($A$1:A1,A2),"",COUNTIF(A:A,A2)) copy down -- __________________________________ HTH Bob "Christopher Naveen" wrote in message ... Hi, I have a col contains numbers and the col also have duplicates. Now i want to count how many duplicates against 1 value. This i can do with countif easily but the important point is if the 1st value has duplicates then it shud say the count in the 1st value and all other duplicate values shud be blank. pls. refer the below example for better understanding, Values Count Remarks 100 3 (Count of 100 in the col is 3) 101 2 (Count of 101 in the col is 2) 100 (100 is duplicate so the count shud be blank) 100 (100 is duplicate so the count shud be blank) 101 (100 is duplicate so the count shud be blank) 102 2 (Count of 102 in the col is 2) 103 1 (Count of 103 in the col is 1) 102 (102 is duplicate so the count shud be blank) 555 1 (555 there is no duplicates in the col and the count is 1) I hope the above example helps to understand better. I request any one of you to help me in this requirement. Thanks in Advance -Chr |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
Thank u so much. it works fine.
"Pete_UK" wrote: Try this in B2: =IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),"") and copy this down. Hope this helps. Pete On Jan 19, 9:34 am, Christopher Naveen wrote: Hi, I have a col contains numbers and the col also have duplicates. Now i want to count how many duplicates against 1 value. This i can do with countif easily but the important point is if the 1st value has duplicates then it shud say the count in the 1st value and all other duplicate values shud be blank. pls. refer the below example for better understanding, Values Count Remarks 100 3 (Count of 100 in the col is 3) 101 2 (Count of 101 in the col is 2) 100 (100 is duplicate so the count shud be blank) 100 (100 is duplicate so the count shud be blank) 101 (100 is duplicate so the count shud be blank) 102 2 (Count of 102 in the col is 2) 103 1 (Count of 103 in the col is 1) 102 (102 is duplicate so the count shud be blank) 555 1 (555 there is no duplicates in the col and the count is 1) I hope the above example helps to understand better. I request any one of you to help me in this requirement. Thanks in Advance -Chr |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Help
You're welcome, Christopher - thanks for feeding back.
Pete On Jan 19, 11:19*am, Christopher Naveen wrote: Thank u so much. it works fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |