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]

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.

 

[Marcelo Branco]

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.

 

[Marcelo Branco]

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.

 

[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.

Perfect!!!!

Thanks Marcelo,

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

Thank you for this

Simon

 

[markmzz]

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:

=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):

=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

 

[MixedUpExcel]

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

 

[markmzz]

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

 

[markmzz]

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

 

[MixedUpExcel]

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.

 

[markmzz]

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