Count if used at higher level

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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
Joined
Apr 29, 2003
Messages
5,905
Platform
MacOS
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
Joined
Nov 14, 2005
Messages
1,690
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
Joined
Nov 14, 2005
Messages
1,690
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%
 

Watch MrExcel Video

Forum statistics

Threads
1,089,761
Messages
5,410,273
Members
403,305
Latest member
tray2014

This Week's Hot Topics

Top