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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Code:
=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.
 
Upvote 0
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.
 
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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