One part number with several substitute numbers

[Sergio]

Hi, I have the below data sample:
A B C
D
PRT STK SUB_SUB_PART SUBSTK
0068605568 0 4970450487 0
0068605726 0 4970450826 16
0068605734 0 0068611051 0
0068612133 1 0068612722 17
0068612133 1 0068611051 0
0068612409 0 0068603220 0
0068612409 0 0068605046 0
0068612721 4 0068612132 3
In this case, there is a part number (column A), quantity of this part (B),
substitute part number (C), and quantity of the substitute.
For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with
qty=o.
Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another
subst. 0068611051 with qty=0.

My question is, how do i know how many parts i have between the part number
and it's substitutes. In the latter case i would have a total of 1+17 parts.
Thanks,

 

[Susan]

um, maybe i'm not understanding the big picture, but in another column
why can't you have:
=b2+d2
=b3+d3
=b4+d4
etc.?
that seems like it would give you your total. you could even put it
in a column waaaay out if you don't want it to interfere with this
data.
just a thought
susan

 

[Sergio]

Thank you Susan, but if you see the latter example, the qty=1 + 17 = 18 in
total. If i add them the way you tell me, i would get 19 in total. Also,
sometimes i can have as many as 23 substitute part numbers, each one with
it's own qty but the original part will still have the same qty row after row.

 

[Shane Devenshire]

Hi,

Try

=IF(A2<>A1,SUMIF(A$2:A$100,A2,B$2$100),"")

this assumes the first part number is on row 2. enter this formula in cell
E2 and copy it down

 

[Rick Rothstein]

Unfortunately, I'm not seeing the big picture either (remember, no one
online here knows how your business model works unless you tells us in
detail). For example, you have this...

0068612133 1 0068612722 17
0068612133 1 0068611051 0

and you say the "answer" is 18 (1+17). Why? There are two 1's, a 0 and a 17
associated with the 0068612133 number in Column A... which "1" is being
added to the 17 and why is the other one being ignored? Or am I misreading
where the 1 is coming from?

 

[Sergio]

You are right, is hard to explain, in english this would read; of part
0068612133 i have 1 part, plus substitute 0068612722 i have 17 parts. The
next line reads part 0068612133 i still have 1 part and subst 0068611051 i
have 0 parts. This is why it adds to 17 parts.
This is part of a bigger formula which would calculate "of this part and all
it's substitutes" I have X parts total.

 

[Rick Rothstein]

Sorry, I'm still a little fuzzy on this. Maybe this will help me
understand... what would the correct answer be for these scenarios?

#1
0068612133 1 0068612722 3
0068612133 2 0068611051 4

#2
0068612133 4 0068612722 8
0068612133 2 0068611051 5

#3
0068612133 1 0068612722 4
0068612133 2 0068611051 3

#4
0068612133 4 0068612722 5
0068612133 2 0068611051 8

 

[T. Valko]

Just a general comment...

Excel usually doesn't handle numbers that start with leading 0s very well.
They almost always cause unforseen problems!

Try something this:

E1 = some part number

=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E1,D)

 

[Sergio]

Those scenarios can't happen, let me explain, column A is the prime part
number, so if this number is the same, it only has one quantity, the number
that could change is in column D. For instance scenario #1; 0068612133 has 1
part but you can have 2 substitute numbers with 3 and 4 parts each number.
This totals 1+3+4=8 in total. Not sure if this is clear..

 

[Vijay]

Hi, Put this formula in E2 and then drag downwords
=SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0,B2)

Assumed your header row starts from A1.

Vijay

 

[Sergio]

Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime
number and some other qty in the substitute column..
SB

 

[T. Valko]

Assuming this is your data:

xxx...1...abc...5
xxx...1...def...2
xxx...1...ghi...3

My understanding is that for part number xxx the correct result should be:

1+5+2+3 = 11

Did you try the formula I suggested in the other branch of this thread?

 

[Sergio]

Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D), but it showed a #N/A, so i changed it
to:
=VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D) being at row 2 and it works except
where the first part number qty=0 and the substitute qty= XX, this always
shows zero as the answer.
I'll keep trying..

 

[Rick Rothstein]

Yes your understanding is OK, i tried your formula:

=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D), but it showed a #N/A,

That wasn't the formula that Biff posted... the E2 in the SUMIF function was
an E1 in his original formula. When I tried the formula he posted
originally, and given his understanding of what you were looking for, it
appeared for work perfectly. I would suggest you retry his original formula
again (copy/paste it rather than retype it).

 

[T. Valko]

......A.....B.....C.....D
1..column headers
2..xxx...0...abc...5
3..xxx...0...def...2
4..xxx...0...ghi...3

Lookup part number xxx

E2 = xxx

=VLOOKUP(E2,A:B,2,0)+SUMIF(A:A,E2,D)

Result = 10

 

[Sergio]

Sorry Rick and Biff, i tried again and i think i can adapt this formula to
the problem i have, it is working.

Thanks,
Sergio

 

[T. Valko]

It sounds like you just need to get the references pointing to the correct
ranges and it should work for you.