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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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
17,017
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,596
Messages
5,832,640
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top