Sumproduct or lookup

[vito]

I have a table of data for multiple types of products manufactured over a
period of time.
X Y Z
Product a b c ...
5 2 3
0 8 11

I am grouping these products in another area
A B C
Group 1 Group 2 Group 3
a b d
c e g
...

What I want to do is based on the group look up the product and some the
number of batches made. I am trying to do a
sumproduct(--(A2:A8=X1:X10),(X2:X10)) however this returms a #VALUE!. I am
assuming because I am trying to search for multiple products instead of 1.
Any help would be great.

Thanks

 

[PCLIVE]

The main problem I see is that your ranges don't match up.
Your firt range is A2:A8, which is a total of seven cells. Your second
range is X1:X10 (a total of 10 cells). Then your third range is a total of
9 cells.
Note: Your ranges don't necessarily have to be the same exact rows, but the
number of cells in each range need to be the same.

May be it should be:
=SUMPRODUCT(--(A2:A10=X2:X10),(X2:X10))

HTH,
Paul

 

[vito]

The range I am looking in contains 25 types of product. From those 25 I will
reference 25 values representing the number of batches for each product. The
number of products in a group i want to look at is 17. The exact formula I
am trying to use is;

SUMPRODUCT(--($H$65:$H$67=$B$38:$D$38),(B3939))
Group Product List # of batches

When i look for a single product by changing the formula to;

SUMPRODUCT(--($H$65=$B$38:$D$38),(B3939))

it works. I just can't seem to get sumproduct to sum multiple values for a
multiple reference.

 

[vito]

Sorry copied the wrong formula;

=SUMPRODUCT(--($H$65:$H$81=$B$38:$Z$38),(B39:Z39))
Group of products All products # of batches

works with

SUMPRODUCT(--($H$65=$B$38:$Z$38),(B39:Z39))
Single product All products # of batches

 

[Jim]

Vito,

Your sumproduct ranges have to be single columns and they all have to have
the same number of cells. You can't do B38:Z38 - trying for a horizontal
array. Even if you rearanged the data, the number of cells in h65:h81 is not
the same as the number of cells in b38:z38. This approach won't work.

 

[Jim]

Vito,

Last post contained a big error. Sorry. You can use horizontal arrays but
you can't mix horizontal and vertical arrays.

Jim