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,

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



On Aug 11, 3:13*pm, Sergio wrote:
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,

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.

"Susan" wrote:

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



On Aug 11, 3:13 pm, Sergio wrote:
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,

Hi,

Try

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

this assumes the first part number is on row 2. enter this formula in cell
E2 and copy it down
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sergio" wrote:

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,

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?

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
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.

"Susan" wrote:

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



On Aug 11, 3:13 pm, Sergio wrote:
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,

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" wrote:

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?

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
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.

"Susan" wrote:

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



On Aug 11, 3:13 pm, Sergio wrote:
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,

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

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
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" wrote:

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?

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
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.

"Susan" wrote:

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



On Aug 11, 3:13 pm, Sergio wrote:
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,

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

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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" wrote:

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?

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
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.

"Susan" wrote:

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



On Aug 11, 3:13 pm, Sergio wrote:
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,

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
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" wrote:

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?

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
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.

"Susan" wrote:

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



On Aug 11, 3:13 pm, Sergio wrote:
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,

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" wrote:

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,

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

"Vijay" wrote:

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" wrote:

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,

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?

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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

"Vijay" wrote:

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" wrote:

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,

Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed it
to:
=VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D: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..

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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

"Vijay" wrote:

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" wrote:

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,

Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D: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).

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed
it
to:
=VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D: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..

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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

"Vijay" wrote:

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" wrote:

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,

......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:D)

Result = 10


--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed
it
to:
=VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D: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..

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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

"Vijay" wrote:

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" wrote:

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,

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

"Rick Rothstein" wrote:

Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D: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).

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed
it
to:
=VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D: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..

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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

"Vijay" wrote:

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" wrote:

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,

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

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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

"Rick Rothstein" wrote:

Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D: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).

--
Rick (MVP - Excel)


"Sergio" wrote in message
...
Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i
changed
it
to:
=VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D: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..

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Sergio" wrote in message
...
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

"Vijay" wrote:

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" wrote:

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,