# Formula to count a specific value within a range of cells in a table

#### Vonsteiner

##### New Member
Okay, so I have a table with the following sample info. In column A I have an ID. There could be multiple duplicates or there could just be one. These IDs will run from cell A5 to probably no more than A10000. I have another column for a fee that is charged to each ID. If there are multiple IDs the fees needs to be spread out over all the IDs.

Column A
1001
1001
1001
1001
1002
1003
1003
1004
1004
1004
1004
1004
1005
1006
1007
1007
1008
etc...

So the fee of \$100 needs to be broken out evenly between each ID. So for ID 1001 the fee would be \$25 per row where 1001 shows up in A. \$100 for 1002 since there is only 1 ID of 1002 and so on. I would like to input a formula into the table that would capture this data. I was thinking of using countif and index was haven't been able to get it to work yet. Any help would be greatly appreciated.

Michael

#### Rick Rothstein

##### MrExcel MVP
Give something like this a try...

=100/COUNTIF(A\$5:A\$10000,A5)

where you would change the red highlighted 10000 to a row number guaranteed to be equal to or greater than the row number of your last data item.

#### jtakw

##### Well-known Member
Hi,

May be you mean this:

Formula copied down, adjust cell references/range as necessary.