Sumproduct & wildcards?

M

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
222
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've put together a test table and formula - see below - which works but I need to make it more flexible with wildcards.. please can someone tell me how?

Table pasted in Cell A1:


[TABLE="width: 346"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Order Type[/TD]
[TD]BOM[/TD]
[TD]Item[/TD]
[TD]Sales Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]8[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]16[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]20[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]24[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]28[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]32[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]VOLTA[/TD]
[TD]VOLTA[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DISPLAY[/TD]
[TD]NOT[/TD]
[TD]VOLTA[/TD]
[TD]34[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NORMAL[/TD]
[TD]VOLTA[/TD]
[TD]NOT[/TD]
[TD]35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DIRECT[/TD]
[TD]NOT[/TD]
[TD]NOT[/TD]
[TD]36[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Formula pasted in Cell G1:
Code: 
=SUMPRODUCT(($B$2:$B$37="VOLTA")*($C$2:$C$37="VOLTA")*($A$2:$A$37<>"DISPLAY")+($B$2:$B$37="VOLTA")*($C$2:$C$37="NOT")*($A$2:$A$37<>"DISPLAY")+($B$2:$B$37="NOT")*($C$2:$C$37="VOLTA")*($A$2:$A$37<>"DISPLAY"),$D$2:$D$37)

The 1's in the table are just for me to be able to check by filtering out the NOT NOT rows!

Next is the bit I don't know how to do:

VOLTA is part of a description (eg. VOLTA DESCRIPTION HERE) - and the word VOLTA may not be at the start or end of the description (will just appear somewhere in that cell of data)

NOT signifies that the word VOLTA is not in that Cell - so I need to be able to change "NOT" in the formula to something that checks if the word VOLTA is not included in that Cells description.

Simple explanation:
I need to sum up the values in the 'Sales Value' Column where 'Display' and 'Volta' both appear in the same Row - Volta can appear in 1 or both of the BOM and Item Columns.

Just in case anyone is curious - BOM = Bill of Materials :)

If there is a better way for me to structure the formula, then please advise.

Thanks in advance.

Simon
 
Sort by date Sort by votes
Marcelo Branco said:
Ok. See if this new version works
=SUMPRODUCT(--(Sheet1!$A$2:$A$37<>"DISPLAY"),--(ISNUMBER(SEARCH("VOLTA",Sheet1!$C$2:$C$37))+ISNUMBER(SEARCH("VOLTA",Sheet1!$E$2:$E$37))>0), Sheet1!$F$2:$F$37)

M.
Click to expand...

Hi Marcelo,

Using your formula, is there a way that I can also put an ignore.. more like 'VOLTA' than 'DISPLAY'. Display is a fixed set of data in a cell; Volta is not fixed as it can appear in any position with other words in the cell.

I have been asked to not include anything that has this in the cells: 'PEARL/VOLTA'

Is there an easy way to adjust your formula to have that exception built into it?

Thanks.

Simon
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi Simon,

Try this too:

=SUMPRODUCT(--(A2:A37<>"Display"),(MMULT(--ISNUMBER(SEARCH("*VOLTA*",C2:E37)),{1;0;1})>0)-
(MMULT(--ISNUMBER(SEARCH("*PEARL/VOLTA*",C2:E37)),{1;0;1})>0),F2:F37)

Markmzz
 
Upvote 0
markmzz said:
Hi Simon,

Try this too:

=SUMPRODUCT(--(A2:A37<>"Display"),(MMULT(--ISNUMBER(SEARCH("*VOLTA*",C2:E37)),{1;0;1})>0)-
(MMULT(--ISNUMBER(SEARCH("*PEARL/VOLTA*",C2:E37)),{1;0;1})>0),F2:F37)

Markmzz
Click to expand...

Hi Markmzz,

I'm trying to get my head around MMULT as I've never used it before and struggling at the moment.

I was hoping that I could take what you have provided and manipulate it to fit my ever changing criteria needs :) but until I understand each section of your formula, I'm guessing I'm going to struggle with that.

What you have provided works perfectly for what I asked for... here comes the unfortunately, I'm trying to get it to fit into the new issue it highlighted.

My table is actually about 4,000 rows of data.

I have Pack Codes in Column B, Pack Descriptions in Column C, Component Codes in Column D and Component Descriptions in Column E; Sales Values are in Column F

Some of the Volta products are compatible with Pearl - which is why they sometimes appear together.

In some cases, the description only starts with "VOL " but as the 3 letters may appear in other words in the description, VOL and a space would only represent VOLTA!

I am tracking Pearl / Volta crossover sales separately...

So, basically, I want to track any Sales that have VOLTA or VOL in the Pack or Component Description.

I don't want to track Sales that have VOL (and a space) in the Pack or Component Description UNLESS THE CORRESPONDING PACK OR CODE DESCRIPTION also has the word VOLTA in it.

And I thought MMULT was complicated :)

I think the below table might sum up my scenario:

^ represents a space after the word as it's more visual than a space!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PACK DESCRIPTION[/TD]
[TD]COMPONENT DESCRIPTION[/TD]
[TD]WANT SALES VALUE[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOLTA^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]NOT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOLTA^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOLTA^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]NOT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOLTA^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]NOT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]NOT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]NOT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]NOT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DON'T WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]PEA/VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]NOT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DON'T WANT[/TD]
[/TR]
[TR]
[TD]NOT[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]PEA/VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DON'T WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]PEA/VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]PEA/VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DON'T WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOLTA^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]PEA/VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]PEA/VOL^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]VOLTA^[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]WANT[/TD]
[/TR]
</tbody>[/TABLE]

Oh, Almost forgot, I have to ignore anything that says 'DISPLAY' in Column A - like the original scenario.

Thanks again for your help so far.

Simon
 
Last edited:
Upvote 0
Hi Simon;

Try this with the last layout (Sheet3):

=SUMPRODUCT((MMULT(--ISNUMBER(SEARCH(" VOLTA^"," "&Sheet3!$A$2:$B$13)),{1;1})>0)+
(MMULT(--ISNUMBER(SEARCH({" VOL^"," VOL^"}," "&Sheet3!$A$2:$B$13)),{1;1})>0),
Sheet3!$C$2:$C$13)

Markmzz
 
Upvote 0
Hi Markmzz,

Thanks for this.

Initial test shows that it appears to work how I'd hope.

Is there an online resource that you can point me to where I can learn to understand this particular formula and how you've put it together, so in the future, I can add it to the various formula's I understand?

Thanks again for your help.

Simon
 
Upvote 0
MixedUpExcel said:
Hi Markmzz,

Thanks for this.

Initial test shows that it appears to work how I'd hope.

Is there an online resource that you can point me to where I can learn to understand this particular formula and how you've put it together, so in the future, I can add it to the various formula's I understand?

Thanks again for your help.

Simon
Click to expand...

Hi Simon,

Look at your InBox Private Message.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

Marcoteo
  • Question Question
If Target.Address change then...automaticaly have to but not working.
Replies
5
Views
2K
offthelip
O
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
137
montecarlo2012
montecarlo2012
L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
781
Domenic
D
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
311
Peter_SSs
Peter_SSs
J
  • Question Question
Formula to move information to calendar
Replies
0
Views
461
jc352
J

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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