# Count if used at higher level

#### gheyman

##### Well-known Member
I have a list. The first column is the Parent Part Number, the second is the individual parts that go into each parent.
I am trying to see if the individual part is used in more than one parent. What I need is to come up with a percentage. its kind of hard to explain. Hope this helps. The Percentage is what I am trying to figure out the code for.

ABC 123 33.3%
ABC 234 50.0%
ABC 123 33.3%
ABC 752 100.0%
DEF 123 33.3%
DEF 234 50.0%
DEF 546 100.0%
GHI 123 33.3%
GHI 753 100.0%

Part 123 is used on all three (ABC, DEF and GHI) so its 33.3%. Even though its used twice on ABC I only count it once. That's what makes it tricky to calculate

#### Marcelo Branco

##### MrExcel MVP
Maybe...

 A​ B​ C​ 1​ Parent​ Parts​ Result​ 2​ ABC​ 123​ 33,30%​ 3​ ABC​ 234​ 50,00%​ 4​ ABC​ 123​ 33,30%​ 5​ ABC​ 752​ 100,00%​ 6​ DEF​ 123​ 33,30%​ 7​ DEF​ 234​ 50,00%​ 8​ DEF​ 546​ 100,00%​ 9​ GHI​ 123​ 33,30%​ 10​ GHI​ 753​ 100,00%​

Array formula in C2 copied down
=1/SUM(IF(FREQUENCY(IF(B\$2:B\$10=B2,MATCH(A\$2:A\$10,A\$2:A\$10,0)),ROW(A\$2:A\$10)-ROW(A\$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

#### gaz_chops

##### Well-known Member
Try

=1/(COUNTIFS(\$B\$1:\$B\$9,\$B1)-(--(COUNTIFS(\$A\$1:\$A\$9,"<>"&\$A1,\$B\$1:\$B\$9,\$B1)>=2)))

Code:
``````[COLOR=#000000][FONT=-webkit-standard][TABLE="width: 261"]
<colgroup><col width="87" span="3" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87"]ABC[/TD]
[TD="width: 87, align: right"]123[/TD]
[TD="class: xl64, width: 87, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC[/TD]
[TD="align: right"]234[/TD]
[TD="class: xl64, align: right"]50.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC[/TD]
[TD="align: right"]123[/TD]
[TD="class: xl64, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC[/TD]
[TD="align: right"]752[/TD]
[TD="class: xl64, align: right"]100.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]DEF[/TD]
[TD="align: right"]123[/TD]
[TD="class: xl64, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]DEF[/TD]
[TD="align: right"]234[/TD]
[TD="class: xl64, align: right"]50.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]DEF[/TD]
[TD="align: right"]546[/TD]
[TD="class: xl64, align: right"]100.0%[/TD]
[/TR]
[TR]
[TD="class: xl63"]GHI[/TD]
[TD="align: right"]123[/TD]
[TD="class: xl64, align: right"]33.3%[/TD]
[/TR]
[TR]
[TD="class: xl63"]GHI[/TD]
[TD="align: right"]753[/TD]
[TD="class: xl64, align: right"]100.0%[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT][/COLOR]``````

#### gheyman

##### Well-known Member
Thanks, but i was looking for VBA Code. My range will vary depending on the data being imported, so these will not work for what I need. But I really appreciate the help.

I take that back, I can use it. Just need to tweak the formula
=1/(COUNTIFS(\$B:\$B,\$B1)-(--(COUNTIFS(\$A:\$A,"<>"&\$A1,\$B:\$B,\$B1)>=2)))

Last edited:

#### gheyman

##### Well-known Member
OK I take that back once again - lol this only works if the part is listed once for the parent. 1234 is listed 4 times but its in three parents, so the answer should be 33.3% like it shows above. However when I use the formula in a sheet it calculates out to 25%