Sumproduct & wildcards?

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
 
Hi Marcelo,

Yes, I've tried the formula from post 2.

You're formula is assuming that the word 'NOT' is in the other cells - if I change the word "NOT" (which is what will actually happen) then my values are no longer correct. In the future, VOLTA won't be the only name I'll be looking for.. so I would need to have some way of specifying that in the formula.

Thanks.
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
To understand the formula above
The condition
1-(Sheet1!$C$2:$C$37="NOT")*(Sheet1!$E$2:$E$37="NOT")
excludes all the rows that contains NOT in column C and NOT in column E.

In other words, includes all the rows that contains VOLTA in either column C or column D.

M.
 
Upvote 0
Hi Marcelo,

Yes, I've tried the formula from post 2.

You're formula is assuming that the word 'NOT' is in the other cells - if I change the word "NOT" (which is what will actually happen) then my values are no longer correct. In the future, VOLTA won't be the only name I'll be looking for.. so I would need to have some way of specifying that in the formula.

Thanks.

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.
 
Upvote 0
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.

Perfect!!!!

Thanks Marcelo,

I've tested it with my other criteria and it's doing exactly what I needed.

Thank you for this :)

Simon
 
Upvote 0
Hi Markmzz,

After further testing, I have come across a (hopefully slight) problem.

This formula (as I have the data on a different sheet to the result) is fine apart from I have a column between the 2 columns where 'VOLTA' resides. I didn't put it in my first example as I didn't foresee the formula using 2 adjacent columns :(. I thought they would be defined as columns in their own right.

This works:
Code:
=SUMPRODUCT(--(Sheet1!A:A>"Display"),--(MMULT(--ISNUMBER(SEARCH("*Volta*",Sheet1!C:D)),{1;1})>0),Sheet1!E:E)

This gives me an error message because I've changed C:D to C:E (E:E has changed to F:F):
Code:
=SUMPRODUCT(--(Sheet1!A:A>"Display"),--(MMULT(--ISNUMBER(SEARCH("*Volta*",Sheet1!C:E)),{1;1})>0),Sheet1!F:F)

As I'm not that familiar with the formula structure of MMULT and possibly ISNUMBER, I'm not sure which bit to change to get it to work?

Thanks.

Hi!

Try this small modification (in red):

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

Markmzz
 
Last edited:
Upvote 0
Thanks Markmzz,

I thought that might be the answer, so I tried that before posting to you... by the way yours work :)

I tried it with a colon and not the semi-colon ... I'd misread what you had in your initial formula... and trying it with the semi-colon works perfectly.

Thank you to both yourself and Marcelo for giving me 2 working solutions.

Really appreciate both of your time on this today.

Thanks.

Simon
 
Upvote 0
Thanks Markmzz,

I thought that might be the answer, so I tried that before posting to you... by the way yours work :)

I tried it with a colon and not the semi-colon ... I'd misread what you had in your initial formula... and trying it with the semi-colon works perfectly.

Thank you to both yourself and Marcelo for giving me 2 working solutions.

Really appreciate both of your time on this today.

Thanks.

Simon

Hi Simon!

You are welcome and thank you for the feedback.

And I'm glad to help you.

Markmzz
 
Upvote 0
Hi!

Try this small modification (in red):

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

Markmzz

A small modification:

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


Markmzz
 
Upvote 0
Hi Markmzz,

Is that basically telling the formula to ignore the 2nd Column (in this case Column D) in the Range of C to E?

So if my Range is E:H and I wanted to just look in Columns E / F & H, then I would restructure the formula to : {1;1;0;1} <<-- This is just an example, not one that I need but interested to see if it works like this :)

Thanks.
 
Upvote 0
Hi Markmzz,

Is that basically telling the formula to ignore the 2nd Column (in this case Column D) in the Range of C to E?

So if my Range is E:H and I wanted to just look in Columns E / F & H, then I would restructure the formula to : {1;1;0;1} <<-- This is just an example, not one that I need but interested to see if it works like this :)

Thanks.

Hi!

Yes, you are right. Even if the word VOLTA there in column G, Excel will ignore it.

Markmzz
 
Upvote 0

Forum statistics

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

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