Sum product looking at a list

turtle81

New Member
Joined
Feb 19, 2019
Messages
9
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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 />
 
Upvote 0
MovementType, Paper, SQMa. Are range names?

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



AB
12345 / 32511 / 45722

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:80px;"><col style="width:140px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>



Yes, exactly like that. It’s not actually my sheet, I just have to work with the format given
 
Upvote 0
What do you expect from a result if more than one option has a coincidence?
 
Upvote 0
Try:

=SUMPRODUCT((MONTH(date)=(MONTH($C$2)))*(YEAR(date)=(YEAR($C$2)))*(MovementType=$I$2)*(ISNUMBER(SEARCH(Paper,$B6))),SQMa)
 
Upvote 0
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
 
Upvote 0
Do not worry, it happens to everyone.
I am gald to help you, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,268
Messages
6,171,099
Members
452,379
Latest member
IainTru

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top