MixedUpExcel
Board Regular
- Joined
- Apr 7, 2015
- Messages
- 222
- Office Version
- 365
- Platform
- 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:
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
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