Using Wildcard in formula obtained from different formula's results

[jjsauer]

Hello,
I have searched everyone online and cannot find the solution to what I believe is a simple question.


Here is a simple version of what I am trying to accomplish. Sum if the value equals a vlookup. There are four possible outcomes. Option 1, Option 2, Option 3 or All of the above (the vLookup would equal 1,2,3 OR *). I cannot change the formula to be something like greater than 0 because I do not want to combine the three other options unless I am combining them all.

=SUMIF(F33:F35,VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, FALSE),G33:G35)

However, when the vlookup pulls in the * the formula fails and will not essentially add everything.

I hope that was clear enough. Please keep in mind, this is just an example. The actual formula is a bit more complicated, as well as an array formula.

Any help with piping in an * via vlookup into another formula would be appreciated.

 

[jjsauer]

<tbody>


</tbody>

Brand					BF									BF
Brand X	$744	$576	$344	$199	$707	$359	$299	$883	$826	Option A	Option A	Option A	Option A	Option A	Option A	Option A	Option A	Option A
Brand Y	$668	$701	$453	$37	$164	$379	$931	$373	$535	Option A	Option B	Option A	Option A	Option B	Option B	Option A	Option B	Option A
Brand Z	$409	$188	$201	$259	$971	$136	$464	$366	$851	Option B	Option A	Option A	Option B	Option A	Option A	Option B	Option A	Option A
Brand X	$455	$203	$127	$840	$87	$96	$134	$323	$511	Option A	Option A	Option B	Option A	Option A	Option A	Option A	Option A	Option B
Brand Y	$796	$113	$277	$360	$365	$232	$49	$926	$897	Option A	Option B	Option A	Option A	Option B	Option B	Option A	Option A	Option A
Brand Z	$638	$770	$169	$930	$997	$279	$333	$372	$418	Option B	Option A	Option A	Option B	Option A	Option A	Option A	Option B	Option A
Brand X	$864	$331	$619	$553	$454	$372	$87	$72	$891	Option A	Option A	Option B	Option A	Option A	Option A	Option B	Option A	Option B
Brand Y	$227	$931	$140	$811	$209	$939	$377	$722	$701	Option A	Option A	Option A	Option A	Option A	Option A	Option A	Option A	Option A
Brand Z	$557	$86	$289	$949	$984	$263	$566	$352	$768	Option A	Option B	Option A	Option A	Option B	Option A	Option A	Option B	Option A
	Brand Y	Option A
Total	$10,777

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

I cannot see how to attach a file, so I hope this works. This is a simplified version of what I have. Cells B14 and C14 are dropdowns with the following options.

B14: Brand X, Brand Y, Brand Z.
C14: Option A, Option B, ALL

Here is the formula in cell B15:
{=SUM(IF($A$2:$A$10=$B$14, IF($K$2:$S$10=SUBSTITUTE(C14, "ALL", "<>"), IF($B$1:$J$1<>"BF", $B$2:$J$10))))}

Where the formula fails is when I try to use "ALL" in the dropdown. I tried using "*" and "<>", but cannot find a way to essentially sum if either Option A or Option B are present...I can only do one or the other. I was looking for a simple solution because I will have to apply this to many formulas...

Thanks!

 

[Aladin Akyurek]

Let's pretend that we have just this:

Row\Col	A​	B​	C​	D​	E​
1​	Brand	BF		BF
2​	Brand X	$10 ​	$15 ​	Option A	Option A
3​	Brand Y	$8 ​	$4 ​	Option A	Option B
4​	Brand Y	$10 ​	$15 ​	Option A	Option B

Would you specify the calculations that you want to make using this sample?

Do this by stating the conditions that must hold along with the manually calculated total...

 

[jjsauer]

Let's pretend that we have just this:

<tbody><tr class="px"wysiwyg_dashes_tr" wysiwyg_cms_table_grid_tr"="" valign="top">[TD="class: grid"]Row\Col[/TD]
[TD="class: grid"]

A​

[/TD]
[TD="class: grid"]

B​

[/TD]
[TD="class: grid"]

C​

[/TD]
[TD="class: grid"]

D​

[/TD]
[TD="class: grid"]

E​

[/TD]
</tr><tr class="wysiwyg_dashes_tr wysiwyg_cms_table_grid_tr" valign="top">[TD="class: grid"]

1​

[/TD]
[TD="class: grid"] Brand [/TD]
[TD="class: grid"] BF [/TD]
[TD="class: grid"][/TD]
[TD="class: grid"] BF [/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_dashes_tr wysiwyg_cms_table_grid_tr" valign="top">[TD="class: grid"]

2​

[/TD]
[TD="class: grid"] Brand X [/TD]
[TD="class: grid"]

$10 ​

[/TD]
[TD="class: grid"]

$15 ​

[/TD]
[TD="class: grid"] Option A [/TD]
[TD="class: grid"] Option A [/TD]
</tr><tr class="wysiwyg_dashes_tr wysiwyg_cms_table_grid_tr" valign="top">[TD="class: grid"]

3​

[/TD]
[TD="class: grid"] Brand Y [/TD]
[TD="class: grid"]

$8 ​

[/TD]
[TD="class: grid"]

$4 ​

[/TD]
[TD="class: grid"] Option A [/TD]
[TD="class: grid"] Option B [/TD]
</tr><tr class="wysiwyg_dashes_tr wysiwyg_cms_table_grid_tr" valign="top">[TD="class: grid"]

4​

[/TD]
[TD="class: grid"] Brand Y [/TD]
[TD="class: grid"]

$10 ​

[/TD]
[TD="class: grid"]

$15 ​

[/TD]
[TD="class: grid"] Option A [/TD]
[TD="class: grid"] Option B [/TD]
</tr></tbody>

Would you specify the calculations that you want to make using this sample?

Do this by stating the conditions that must hold along with the manually calculated total...

For the sake of simplifying this further, ignore the "BF", otherwise your example above would be too small.

I would like to have 2 dropdown menus that allow you to select Brand and Option with the following selection items:
Brand: Brand X & Brand Y
Option: Option A, Option B, Both Option A & B

In you example, selecting:
Brand X and Option A = $25
Brand X and Option B = $0
Brand X and Both Option A & B = $25
Brand Y and Option A = $18
Brand Y and Option B = $19
Brand Y and Both Option A & B = $37


My problem is a simple array formula to calculate this in one cell. Originally I had hoped that I could add a wildcard when qualifying the Option, allowing for either to count as True, but I couldn't get that to work, not would your "<>" substitute with text or values. Again, I was hoping for something simple rather than having to repeat my formula multiple times to fit with an extended IF/THEN formula because I would have to make this change across many formulas and then any future changes I would really be open to making mistakes.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF($A$2:$A$4=A6,IF($D$2:$E$4=IF($B6="All",$D$2:$E$4,$B6),$B$2:$C$4)))

A6 houses a brand drop down; B6 an option dropdown which includes Option A, Option B, and All as possible choices.

 

[jjsauer]

Control+shift+enter, not just enter:

=SUM(IF($A$2:$A$4=A6,IF($D$2:$E$4=IF($B6="All",$D$2:$E$4,$B6),$B$2:$C$4)))

A6 houses a brand drop down; B6 an option dropdown which includes Option A, Option B, and All as possible choices.

Aladin, thanks! That works in my earlier example spreadsheet. Seems simple enough to integrate across all my formulas. I was not aware that you could reference a range within an array formula. For my own education could you state what is happening there? Specifically when ="All" the formula would be IF($D$2:$E$4=$D$2:$E$4)=TRUE. What is Excel doing there? In theory could I compare two arrays like that to see if they match???

Thanks for your patience and help!

 

[Aladin Akyurek]

Aladin, thanks! That works in my earlier example spreadsheet. Seems simple enough to integrate across all my formulas. I was not aware that you could reference a range within an array formula. For my own education could you state what is happening there? Specifically when ="All" the formula would be IF($D$2:$E$4=$D$2:$E$4)=TRUE. What is Excel doing there? In theory could I compare two arrays like that to see if they match???

Thanks for your patience and help!

When B6 = all, the equality test D2:E4 = D2:E4 is run, yielding a bunch of TRUE's, i.e. 6 TRUE values. Thus all dollar values from the rows of B2:C4 where A2:A4 = A6.