Count if used at higher level

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,347
Office Version
  1. 365
Platform
  1. Windows
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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]
 
Upvote 0
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:
Upvote 0
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%
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

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

Which adblocker are you using?

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
Back
Top