Sumproduct with Index / Match or Vlookup not returning array

Ron0813

New Member
Hello –
Similar to challenges others have described here, I’m having troubles with using Vlookup Index/Match along with Sumproduct. Those two functions don’t want to return an array in my formula.

Scenario:
I have an invoice table with an Invoice #, Item Code, Sales Price, and Quantity. Each Item Code is associated with a Category which is in a lookup table (not in the Invoice table). I do not want to create a helper column. I am trying to determine revenue for a given Category (For Category, multiply Sales Price by Quantity and sum across all invoices). I have been using the following formula, tables below as well. Invoice table on the left and look up table on the right. Not real data, I’m simplifying.

=SUMPRODUCT($C$2:$C$6,$D$2:$D$6,--(INDEX($G$2:$G$6,MATCH($B$2:$B$6,$F$2:$F$6,0))=$B$11))

I'm supplying the Category as an input (B11) and using True/False return from the Index formula (Array 3 in Sumproduct) to filter on the Category. That is, if it returns 0 the values in that row will not be summed.

Column A
Invoice NumberItem CodeSales Price Per UnitQuantityItem CodeCategory
00001134.95111D
00002115.2672B
00003326.95233B
00004355.95304C
0000554.95115D

<tbody>
</tbody>
 

DanteAmor

Well-known Member
Hi @★ Ron0813, Welcome to the forum!

Try this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:47px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Invoice Number</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Item Code</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Sales Price Per Unit</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Quantity</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Item Code</td><td style="background-color:#fafafa; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Category</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">34.95</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">11</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">15.26</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">7</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">26.95</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">23</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">55.95</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">30</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">4.95</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">11</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; ">D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >B</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="text-align:right; ">726.67</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B12</td><td >=SUMPRODUCT((G2:G6=B11)*(C2:C6)*(D2:D6))</td></tr></table></td></tr></table>
 
Last edited:

Marcelo Branco

MrExcel MVP
Ron,

What is the expected result for C11= B?

Not sure i understood what you need, but i'm supposing you want to calculate the total for invoices 3 and 4 because such invoices belong to Category B (Item code = 3).
In this case the result should be 2298.35 (26.95*23 + 55.95*30)
Could you clarify?

M.
 

Marcelo Branco

MrExcel MVP
If i'm right maybe...

In order to force the INDEX function to return an array is tricky.
An extra "push" is needed - something like...

=SUMPRODUCT($C$2:$C$6,$D$2:$D$6,--(INDEX($G$2:$G$6,N(IF(1,MATCH($B$2:$B$6,$F$2:$F$6,0))))=$B$11))
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 

Eric W

MrExcel MVP
Assuming the same logic as Marcelo, here's a shorter formula:

=SUMPRODUCT($C$2:$C$6,$D$2:$D$6,COUNTIFS($F$2:$F$6,$B$2:$B$6,$G$2:$G$6,B11))
 

Marcelo Branco

MrExcel MVP
Another (simpler) formula

=SUMPRODUCT($C$2:$C$6,$D$2:$D$6,--(LOOKUP(B2:B6,F2:F6,G2:G6)=$B$11))
confirmed with just Enter

M.
 
Last edited:

Marcelo Branco

MrExcel MVP
Just for fun...

Another
=SUMPRODUCT(--ISNUMBER(MATCH(B$2:B$6,F$2:F$6*(G$2:G$6=B$11),0)),C$2:C$6,D$2:D$6)

M.
 

Ron0813

New Member
Hi @★ Ron0813, Welcome to the forum!

Try this:

ABCDEFG
1Invoice NumberItem CodeSales Price Per UnitQuantity Item CodeCategory
21134.9511 1D
32115.267 2B
43326.9523 3B
54355.9530 4C
6554.9511 5D
7
8
9
10
11 B
12 726.67

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
</tbody>

CellFormula
B12=SUMPRODUCT((G2:G6=B11)*(C2:C6)*(D2:D6))

<tbody>
</tbody>

<tbody>
</tbody>
Thank you Dante! The expected result for B would actually be 2298.35. The two Item Codes associated with B are #2 and #3 . Only Item Code #3 is in the Invoice table. If you do the Sumproduct on the two rows for Item Code B in the Invoice total you get the 2298.35. I am wondering if I threw you off also by the way I pasted the columns. There are two tables separated by column E. So I am trying to figure out how to look up the Item Code to Category association from the lookup table and use that when the arrays are being process in the Invoice table. Hope that makes sense and thank you again for your input!
 

Ron0813

New Member
That's right Marcelo! I will take a look at your other posts and see how those solutions work. Thank you for the input!
 

Ron0813

New Member
That definitely works Marcelo. Thank you for this. I'm not that familiar with Lookup but will go learn about it and make sure this works in my larger dataset/problem. Thank you so much for this. Wish I had posted earlier, I think I spent 8 hours fooling with this. You just saved me a bunch of time.
 

Some videos you may like

This Week's Hot Topics

Top