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

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
Joined
Dec 3, 2018
Messages
15,092
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Feb 19, 2019
Messages
9
MovementType, Paper, SQMa. Are range names?

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



AB
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
Joined
Dec 3, 2018
Messages
15,092
Office Version
  1. 2010
Platform
  1. Windows
What do you expect from a result if more than one option has a coincidence?
 

turtle81

New Member
Joined
Feb 19, 2019
Messages
9

ADVERTISEMENT

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
Joined
Dec 3, 2018
Messages
15,092
Office Version
  1. 2010
Platform
  1. Windows
Try:

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

turtle81

New Member
Joined
Feb 19, 2019
Messages
9
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
Joined
Dec 3, 2018
Messages
15,092
Office Version
  1. 2010
Platform
  1. Windows
Do not worry, it happens to everyone.
I am gald to help you, thanks for the feedback.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top