Count if used at higher level

gheyman

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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,921
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,694
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,694
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,090,214
Messages
5,413,125
Members
403,463
Latest member
kraamerica

This Week's Hot Topics

Top