Count if used at higher level

[gheyman]

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]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Parent​

[/td][td]

Parts​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

ABC​

[/td][td]

123​

[/td][td]

33,30%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

ABC​

[/td][td]

234​

[/td][td]

50,00%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

ABC​

[/td][td]

123​

[/td][td]

33,30%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

ABC​

[/td][td]

752​

[/td][td]

100,00%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

DEF​

[/td][td]

123​

[/td][td]

33,30%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

DEF​

[/td][td]

234​

[/td][td]

50,00%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

DEF​

[/td][td]

546​

[/td][td]

100,00%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

GHI​

[/td][td]

123​

[/td][td]

33,30%​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

GHI​

[/td][td]

753​

[/td][td]

100,00%​

[/td][/tr]
[/table]

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]

Try

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

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

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

 

[gheyman]

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%