NUmber of items that appear more than once

[gheyman]

I have a list a part Numbers down column B. I want to know how many of those part numbers appear more than once in the list. Is there a formula for that? right now I have a count formula down a column and then count how many are not equal to one? I would rather just have a formula in one cell that gives me that answer. Something similar to counting unique items (=SUMPRODUCT(1/COUNTIF(BOM__Table1[ItemID],BOM__Table1[ItemID]))-1)

 

[DanteAmor]

Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >dam</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >dam</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >jor</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >ven</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >jor</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >ven</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >sdf</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >dam</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >xxc</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >B2</td><td >{=SUM(IF(FREQUENCY(IF(A2:A14<>"",MATCH(A2:A14,A2:A14,0)),ROW(A2:A14)-ROW(A2)+1),1))}</td></tr></table></td></tr></table>


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[DanteAmor]

If they appear 2 or more times, then it should be like this:

[TABLE="class: grid, width: 800"] <tbody>[TR] [TD]{=SUM(IF(FREQUENCY(IF(A2:A14<>"",MATCH(A2:A14,A2:A14,0)),ROW(A2:A14)-ROW(A2)+1)-1>0,1))}

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[gheyman]

Thanks, but what I am looking for, if I use your table above, should be me an answer of "3"
1 Dam is in the list more than once
2 Jor is in the list more than one
3 Ven is in the list more than once

I appreciate your time and effort. sorry I wasn't clearer.

 

[DanteAmor]

Thanks, but what I am looking for, if I use your table above, should be me an answer of "3"
1 Dam is in the list more than once
2 Jor is in the list more than one
3 Ven is in the list more than once

I appreciate your time and effort. sorry I wasn't clearer.

You tried the formula in post #3

 

[gheyman]

I did and got a answer of 0. Now I see formula didn't take as an Array. Thanks That worked

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.