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

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
32
Office Version
365
Platform
Windows
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
Joined
Apr 18, 2011
Messages
35,561
Office Version
2010
Platform
Windows
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
Joined
Jun 29, 2014
Messages
5,146
Hi,

May be you mean this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ID</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">IDs</td><td style=";">Fee</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1001</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;">1001</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1001</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;">1002</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1001</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;">1003</td><td style="text-align: right;;">500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1001</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;">1004</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1002</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;">1005</td><td style="text-align: right;;">300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1003</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td><td style="text-align: right;;">1006</td><td style="text-align: right;;">400</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1003</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td><td style="text-align: right;;">1007</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1004</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;">1008</td><td style="text-align: right;;">1200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">1004</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1004</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1004</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">1004</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">1005</td><td style="text-align: right;;">300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">1006</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">1007</td><td style="text-align: right;;">1000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">1007</td><td style="text-align: right;;">1000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">1008</td><td style="text-align: right;;">1200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet90</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,D$5:E$12,2</font>)/COUNTIFS(<font color="Blue">A$5:A$10000,A5</font>)</td></tr></tbody></table></td></tr></table><br />

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

Forum statistics

Threads
1,084,878
Messages
5,380,415
Members
401,673
Latest member
Ali Balleya

Some videos you may like

This Week's Hot Topics

Top