Sumproduct & wildcards?

[MixedUpExcel]

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:

Order Type	BOM	Item	Sales Value
DISPLAY	VOLTA	VOLTA	1
NORMAL	NOT	VOLTA	2
DIRECT	VOLTA	NOT	3
DISPLAY	NOT	NOT	4	1
NORMAL	VOLTA	VOLTA	5
DIRECT	NOT	VOLTA	6
DISPLAY	VOLTA	NOT	7
NORMAL	NOT	NOT	8	1
DIRECT	VOLTA	VOLTA	9
DISPLAY	NOT	VOLTA	10
NORMAL	VOLTA	NOT	11
DIRECT	NOT	NOT	12	1
DISPLAY	VOLTA	VOLTA	13
NORMAL	NOT	VOLTA	14
DIRECT	VOLTA	NOT	15
DISPLAY	NOT	NOT	16	1
NORMAL	VOLTA	VOLTA	17
DIRECT	NOT	VOLTA	18
DISPLAY	VOLTA	NOT	19
NORMAL	NOT	NOT	20	1
DIRECT	VOLTA	VOLTA	21
DISPLAY	NOT	VOLTA	22
NORMAL	VOLTA	NOT	23
DIRECT	NOT	NOT	24	1
DISPLAY	VOLTA	VOLTA	25
NORMAL	NOT	VOLTA	26
DIRECT	VOLTA	NOT	27
DISPLAY	NOT	NOT	28	1
NORMAL	VOLTA	VOLTA	29
DIRECT	NOT	VOLTA	30
DISPLAY	VOLTA	NOT	31
NORMAL	NOT	NOT	32	1
DIRECT	VOLTA	VOLTA	33
DISPLAY	NOT	VOLTA	34
NORMAL	VOLTA	NOT	35
DIRECT	NOT	NOT	36	1

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Formula pasted in Cell G1:

=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

 

[MixedUpExcel]

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.

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

 

[markmzz]

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

 

[MixedUpExcel]

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

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!

PACK DESCRIPTION	COMPONENT DESCRIPTION	WANT SALES VALUE
VOLTA^ <tbody> </tbody>	NOT <tbody> </tbody>	WANT
VOLTA^ <tbody> </tbody>	VOLTA^ <tbody> </tbody>	WANT
NOT <tbody> </tbody>	VOLTA^ <tbody> </tbody>	WANT
VOL^ <tbody> </tbody>	NOT <tbody> </tbody>	WANT
NOT <tbody> </tbody>	VOL^ <tbody> </tbody>	WANT
VOL^ <tbody> </tbody>	VOL^ <tbody> </tbody>	WANT
NOT <tbody> </tbody>	NOT <tbody> </tbody>	DON'T WANT
PEA/VOL^ <tbody> </tbody>	NOT <tbody> </tbody>	DON'T WANT
NOT	PEA/VOL^ <tbody> </tbody>	DON'T WANT
PEA/VOL^ <tbody> </tbody>	PEA/VOL^ <tbody> </tbody>	DON'T WANT
VOLTA^ <tbody> </tbody>	PEA/VOL^ <tbody> </tbody>	WANT
PEA/VOL^ <tbody> </tbody>	VOLTA^ <tbody> </tbody>	WANT

<tbody>
</tbody>

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

 

[markmzz]

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

 

[MixedUpExcel]

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

 

[markmzz]

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

Hi Simon,

Look at your InBox Private Message.

Markmzz

 

[MixedUpExcel]

Hi Markmzz,

Have replied to your private message.

Thank you again.

Simon

 

[markmzz]

Hi!

You're welcome.

Have replied to your private message.

Markmzz