SUMPRODUCT to index and array of string values

D4rwin

Board Regular
Joined
Mar 31, 2014
Messages
91
Here's a tricky Sumproduct question which I'm getting stuck on. I want to sum the Quantity in Table 2 with a lookup on the group criteria in Table1.
The most logical solution would be to add another column next to Quantity and populate it with a lookup on group. However, this question is purely out of technical curiosity and so I'm wondering if anyone has any ideas how to get this methodology to work.

=SUMPRODUCT(Quantity*(Product=(INDEX(LookProduct, MATCH("X", LookGroup, 0)))))

At the moment Index is of course only returning the first matched value - "A" - but I would like to find a way to index the array of LookProduct associated with each LookGroup (x and y).

To demonstrate more clearly what it is I'm looking for (referencing Group "X"):
=SUMPRODUCT(Quantity*(Product={"A", "B"}))

Table1

LookProductLookGroup
AX
BY
CY
DX

<tbody>
</tbody>

Table2

ProductQuantity
a12
a23
a43
b432
c14
b32
d61
d19
a27

<tbody>
</tbody>

Any clues??
Thanks
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,823
To demonstrate more clearly what it is I'm looking for (referencing Group "X"):
=SUMPRODUCT(Quantity*(Product={"A", "B"}))

Table1

LookProduct
LookGroup
A
X
B
Y
C
Y
D
X

<tbody>
</tbody>

Hi

Shouldn't group X be {"A","D"}?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Maybe....


A
B
C
D
E
F
G
H
1
Group​
Quantity​
LookProduct​
LookGroup​
Product​
Quantity​
2
X​
185​
A​
X​
a​
12​
3
Y​
478​
B​
Y​
a​
23​
4
C​
Y​
a​
43​
5
D​
X​
b​
432​
6
c​
14​
7
b​
32​
8
d​
61​
9
d​
19​
10
a​
27​

Formula in B2 copied down
=SUMPRODUCT(SUMIF($G$2:$G$10,$D$2:$D$5,$H$2:$H$10),--($E$2:$E$5=A2))

Hope this helps

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,965
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top