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
|