# Sum product looking at a list

#### turtle81

##### New Member
I have a working sum product

=(SUMPRODUCT((MONTH(date)=(MONTH(\$C\$2)))*(YEAR(date)=(YEAR(\$C\$2)))*(MovementType=\$I\$2)*(Paper=\$B6),SQMa))

the problem is

[FONT=&quot](Paper=\$b6) b6 could contain more than one product code variation eg 12345 / 32511 / 45722

is there a way to make it look for the all of the options? [/FONT]

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

#### DanteAmor

##### Well-known Member
MovementType, Paper, SQMa. Are range names?

How do you have the data in the cell, separated by a diagonal?
Like this:

<b></b><br /><br /><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:80px;" /><col style="width:140px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >12345 / 32511 / 45722 </td></tr></table> <br /><br />

#### turtle81

##### New Member
MovementType, Paper, SQMa. Are range names?

How do you have the data in the cell, separated by a diagonal?
Like this:

 A B 1 2 3 4 5 6 12345 / 32511 / 45722

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

Yes, exactly like that. It’s not actually my sheet, I just have to work with the format given

#### DanteAmor

##### Well-known Member
What do you expect from a result if more than one option has a coincidence?

#### turtle81

##### New Member

What do you expect from a result if more than one option has a coincidence?

It looks in a list for the numbers and if one of them is there then it sums up

#### DanteAmor

##### Well-known Member
Try:

=SUMPRODUCT((MONTH(date)=(MONTH(\$C\$2)))*(YEAR(date)=(YEAR(\$C\$2)))*(MovementType=\$I\$2)*(ISNUMBER(SEARCH(Paper,\$B6))),SQMa)

#### turtle81

##### New Member
Try:

=SUMPRODUCT((MONTH(date)=(MONTH(\$C\$2)))*(YEAR(date)=(YEAR(\$C\$2)))*(MovementType=\$I\$2)*(ISNUMBER(SEARCH(Paper,\$B6))),SQMa)

sorry for not getting back to you sooner, I got caught up in another work project.

this works perfectly thanks

#### DanteAmor

##### Well-known Member
Do not worry, it happens to everyone.

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.

1,168,030
Messages
5,856,935
Members
431,841
Latest member
jaybeem

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