# SUMPRODUCT to index and array of string values

#### D4rwin

##### Board Regular
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

 LookProduct LookGroup A X B Y C Y D X

<tbody>
</tbody>

Table2

 Product Quantity a 12 a 23 a 43 b 432 c 14 b 32 d 61 d 19 a 27

<tbody>
</tbody>

Any clues??
Thanks

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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"}?

yes, absolutely correct. My mistake.

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.

Right on the the spot!

Replies
11
Views
225
Replies
5
Views
560
Replies
6
Views
812
Replies
4
Views
857
Replies
19
Views
518

1,203,752
Messages
6,057,151
Members
444,908
Latest member
Jayrey

### 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.

### Which adblocker are you using?

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

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