# 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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.

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]``````

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:
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%

Replies
14
Views
419
Replies
0
Views
1K
Replies
5
Views
2K
Replies
3
Views
489
Replies
4
Views
610

Threads
1,203,171
Messages
6,053,876
Members
444,692
Latest member
Queendom

### 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

### 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