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
<tbody>
</tbody>
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 Number | Item Code | Sales Price Per Unit | Quantity | Item Code | Category | |
| 00001 | 1 | 34.95 | 11 | 1 | D | |
| 00002 | 1 | 15.26 | 7 | 2 | B | |
| 00003 | 3 | 26.95 | 23 | 3 | B | |
| 00004 | 3 | 55.95 | 30 | 4 | C | |
| 00005 | 5 | 4.95 | 11 | 5 | D | |
<tbody>
</tbody>
