Vlookup and SumIf Problem

  • Thread starter Thread starter andyj
  • Start date Start date
A

andyj

I'm trying to combine Vlookup and Sumif, but so far my attempts have
failed!!

I have a 2 sheet workbook, the first sheet contains a lookup table,
like this:

Model ProductType
a small
b large
c small

The second sheet the contains a list of rejects, including the model,
the process @ which it was rejected, and the quantity of rejects.

model process qty
a x 1
b y 2
c z 2
a y 1
b z 1
c x 3
a z 2
b x 1
c y 4


I'm using Vlookup to assign a product type to each of the lines in
sheet 2.

I now want to add up the number of rejects at Each process, by Product
Type.

The Qty of lines in sheet 2 is unlimited, and to reduce the file size,
I do not want to a single column to use Vlookup and then a seperate
table to use SumIf to add up the rejects.

I want to try and combine the two function.

If it helps, I have a sample file I can attach, by request.
 
I'm trying to combine Vlookup and Sumif, but so far my attempts have
failed!!

I have a 2 sheet workbook, the first sheet contains a lookup table,
like this:

Model ProductType
a small
b large
c small
Click to expand...

Name this MPT, excluding the top row, and assume its first column is sorted in
ascending order.
The second sheet the contains a list of rejects, including the model,
the process @ which it was rejected, and the quantity of rejects.

model process qty
a x 1
b y 2
c z 2
a y 1
b z 1
c x 3
a z 2
b x 1
c y 4
Click to expand...

Name this MPQ, excluding the top row, but makeing no assumptions about ordering.
I'm using Vlookup to assign a product type to each of the lines in
sheet 2.
Click to expand...

Presumably you don't have any problems doing this.
I now want to add up the number of rejects at Each process, by Product
Type.

The Qty of lines in sheet 2 is unlimited, and to reduce the file size,
I do not want to a single column to use Vlookup and then a seperate
table to use SumIf to add up the rejects.

I want to try and combine the two function.
Click to expand...

For product type 'small' and process 'x' you could use

=SUMPRODUCT((LOOKUP(INDEX(MPQ,0,1),MPT)="small")*(INDEX(MPQ,0,2)="x")
*INDEX(MPQ,0,3))

In this particular case *AND* assuming MPT is sorted as assumed, you want to use
LOOKUP rather than VLOOKUP.
 
Back
Top