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]
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
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
12,593
Office Version
  1. 2007
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
12,593
Office Version
  1. 2007
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
12,593
Office Version
  1. 2007
Platform
  1. Windows
Do not worry, it happens to everyone.
I am gald to help you, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,708
Messages
5,524,431
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top