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

[Vonsteiner]

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]

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]

Hi,

May be you mean this:

Book1
	A	B	C	D	E
4	ID			IDs	Fee
5	1001	25		1001	100
6	1001	25		1002	100
7	1001	25		1003	500
8	1001	25		1004	1000
9	1002	100		1005	300
10	1003	250		1006	400
11	1003	250		1007	2000
12	1004	200		1008	1200
13	1004	200
14	1004	200
15	1004	200
16	1004	200
17	1005	300
18	1006	400
19	1007	1000
20	1007	1000
21	1008	1200
Sheet90

Cell Formulas
Range	Formula
B5	=VLOOKUP(A5,D$5:E$12,2)/COUNTIFS(A$5:A$10000,A5)

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