Sumproduct or lookup
 

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

Sumproduct or lookup
 

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

Sumproduct or lookup
 

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),(B39:D39))
Group Product List # of batches

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

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

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



"PCLIVE" wrote:

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

Sumproduct or lookup
 

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

"vito" wrote:

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),(B39:D39))
Group Product List # of batches

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

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

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



"PCLIVE" wrote:

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

Sumproduct or lookup
 

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.

"vito" wrote:

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

"vito" wrote:

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),(B39:D39))
Group Product List # of batches

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

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

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



"PCLIVE" wrote:

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

Sumproduct or lookup
 

Vito,

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

Jim

"vito" wrote:

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

"vito" wrote:

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),(B39:D39))
Group Product List # of batches

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

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

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



"PCLIVE" wrote:

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