Sumproduct with Index / Match or Vlookup not returning array

[Ron0813]

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
[TABLE="width: 607"]
<tbody>[TR]
[TD]Invoice Number[/TD]
[TD]Item Code[/TD]
[TD]Sales Price Per Unit[/TD]
[TD]Quantity[/TD]
[TD][/TD]
[TD]Item Code[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]00001[/TD]
[TD]1[/TD]
[TD]34.95[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]00002[/TD]
[TD]1[/TD]
[TD]15.26[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]00003[/TD]
[TD]3[/TD]
[TD]26.95[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]00004[/TD]
[TD]3[/TD]
[TD]55.95[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]00005[/TD]
[TD]5[/TD]
[TD]4.95[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

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>

 

[Marcelo Branco]

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]

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]

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]

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.

 

[Marcelo Branco]

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]

Hi @★ Ron0813, Welcome to the forum!

Try this:

	A	B	C	D	E	F	G
	B

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Invoice Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Item Code[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Sales Price Per Unit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Quantity[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Item Code[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]Category[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]34.95[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]D[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]15.26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]B[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]26.95[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]B[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]55.95[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]C[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]4.95[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] "]D[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]726.67[/TD]

</tbody>

Cell Formula 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]

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]

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.