kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
355
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
Hello Folks ,

I have Column A in which Id's are given.In Column B i need to count the Instances how many times a particular ID has occurred.

I can do this with Count If Formula however by this way it with give me data like below

Case NumberCount
CS9243061141114
CS924306114111 3
CS9243061141112
CS9243061141111
A141904343
A141904342
A141904341
A103128051
CS9228091261954
CS9228091261953
CS9228091261952
CS9228091261951

<colgroup><col span="2"></colgroup><tbody>
</tbody>

I need it this way

Case NumberCount
CS924306114111 4
CS9243061141114
CS9243061141114
CS9243061141114
A141904343
A141904343
A141904343
A103128051
CS9228091261954
CS9228091261954
CS9228091261954
CS9228091261954

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Any possible way ??
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,420
Office Version
  1. 365
Platform
  1. Windows
Still with COUNTIF, like this?

<b>Countif</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:145px;" /><col style="width:56px;" /></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:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Case Number</td><td style="font-size:10pt; text-align:right; ">Count</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">CS924306114111</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">CS924306114111</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">CS924306114111</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">CS924306114111</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">A14190434</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">A14190434</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">A14190434</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">A10312805</td><td style="font-size:10pt; text-align:right; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">CS922809126195</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">CS922809126195</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; ">CS922809126195</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; ">CS922809126195</td><td style="font-size:10pt; text-align:right; ">4</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>Spreadsheet Formulas</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 >Formula</td></tr><tr><td >B2</td><td >=COUNTIF(A$2:A$13,A2)</td></tr></table></td></tr></table>
 
Last edited:

Forum statistics

Threads
1,136,323
Messages
5,675,091
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top