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

 

[Marcelo Branco]

Observing the formula in G1 it seems you want to sum rows where:
Column A <> DISPLAY
and
the pair Column B-Column C is different from NOT - NOT

If i'm right maybe this formula does what you need
=SUMPRODUCT(--($A$2:$A$37<>"DISPLAY"),1-($B$2:$B$37="NOT")*($C$2:$C$37="NOT"),$D$2:$D$37)

Hope this helps

M.

 

[markmzz]

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.

Hi!

Try this too:

=SUMPRODUCT(--(A2:A37="Display"),--(MMULT(--ISNUMBER(SEARCH("*Volta*",B2:C37)),{1;1})>0),D2:D37)

and tell me if works.

Markmzz

 

[Marcelo Branco]

Observing the formula in G1 it seems you want to sum rows where:
Column A <> DISPLAY
and
the pair Column B-Column C is different from NOT - NOT

If i'm right maybe this formula does what you need
=SUMPRODUCT(--($A$2:$A$37<>"DISPLAY"),1-($B$2:$B$37="NOT")*($C$2:$C$37="NOT"),$D$2:$D$37)

Hope this helps

M.

If your conditions are
Column A=DISPLAY (instead of Column A<>DISPLAY )
and
the pair Column B-Column C is different from NOT - NOT

the formula above should be adjusted to
=SUMPRODUCT(--($A$2:$A$37="DISPLAY"),1-($B$2:$B$37="NOT")*($C$2:$C$37="NOT"),$D$2:$D$37)

M.

 

[MixedUpExcel]

Observing the formula in G1 it seems you want to sum rows where:
Column A <> DISPLAY
and
the pair Column B-Column C is different from NOT - NOT

If i'm right maybe this formula does what you need
=SUMPRODUCT(--($A$2:$A$37<>"DISPLAY"),1-($B$2:$B$37="NOT")*($C$2:$C$37="NOT"),$D$2:$D$37)

Hope this helps

M.

Hi Marcelo,

Your logic is sound apart from one thing... "NOT" has to actually be "not any cell which contains the word VOLTA in any position within that Cell's sentence" - so in my head - "<>*VOLTA") but I don't know how to put that in the Sumproduct structure.

Any ideas?

Thanks.

 

[MixedUpExcel]

Hi!

Try this too:

=SUMPRODUCT(--(A2:A37="Display"),--(MMULT(--ISNUMBER(SEARCH("*Volta*",B2:C37)),{1;1})>0),D2:D37)

and tell me if works.

Markmzz

Hi Markmzz,

Thanks for this.. just tried it and I think it works

I didn't want any values SUM'd which are against 'Display' - so I've just changed it to:

=SUMPRODUCT(--(A2:A37<>"Display"),--(MMULT(--ISNUMBER(SEARCH("*Volta*",B2:C37)),{1;1})>0),D2:D37)

Want to test it a bit more as I'm not familiar with the MMULT and the structure you've used but looks good so far.

Thank you.

 

[Marcelo Branco]

Hi Marcelo,

Your logic is sound apart from one thing... "NOT" has to actually be "not any cell which contains the word VOLTA in any position within that Cell's sentence" - so in my head - "<>*VOLTA") but I don't know how to put that in the Sumproduct structure.

Any ideas?

Thanks.

I assumed that the cells that are equal to NOT do not contain the word VOLTA and that all the cells different from NOT do contain the word VOLTA.
If this is not correct try to provide a data sample (~10 rows) that correspond to your real scenario.

M.

 

[MixedUpExcel]

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.

 

[MixedUpExcel]

I assumed that the cells that are equal to NOT do not contain the word VOLTA and that all the cells different from NOT do contain the word VOLTA.
If this is not correct try to provide a data sample (~10 rows) that correspond to your real scenario.

M.

Hi Marcelo,

You're correct - NOT does not contain the word VOLTA.

How would I change your formula, for the NOT part of the formula, so that it finds the cell which does NOT contain the word VOLTA? That was why I put the example: "<>*VOLTA" which I thought represented DOES NOT EQUAL WILDCARD VOLTA WILDCARD but I'm not sure how to put that into the structure of your formula.. everything else was correct.

Thanks for your help.

 

[Marcelo Branco]

Hi Marcelo,

You're correct - NOT does not contain the word VOLTA.

How would I change your formula, for the NOT part of the formula, so that it finds the cell which does NOT contain the word VOLTA? That was why I put the example: "<>*VOLTA" which I thought represented DOES NOT EQUAL WILDCARD VOLTA WILDCARD but I'm not sure how to put that into the structure of your formula.. everything else was correct.

Thanks for your help.

Have you tried the formula in post 2?

Repeating and adjusting the columns according to your post 8 - see if this works
=SUMPRODUCT(--(Sheet1!$A$2:$A$37<>"DISPLAY"),1-(Sheet1!$C$2:$C$37="NOT")*(Sheet1!$E$2:$E$37="NOT"),Sheet1!$F$2:$F$37)

M.