Sunday, October 14, 2012

yogi_Workaround For A WildCard In Use With FILTER SUM(IF And QUERY

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #807  Oct 14, 2012    www.energyefficientbuild.com.

user Jacob Bockelmann said: (http://productforums.google.com/forum/?zx=3rrg4qfnsz5v#!category-topic/docs/spreadsheets/U8-SyPmJ0D4)
Wildcard in SUMIF( or sum(filter( - Please help
Hello,

I have a series of sum(filter( formulas in a project just like the one below:

=IFERROR(SUM(FILTER(Brian!$D:$D,Brian!$B:$B=Settings!$A$2,Brian!$O:$O=$C$3,Brian!$N:$N=A7,Brian!$AK:$AK>=$F$3,Brian!$AK:$AK<$G$3)),0)

What I'd like to do is utilize a wildcard for some of the filter conditions; for example:

=IFERROR(SUM(FILTER(Brian!$D:$D,Brian!$B:$B=Settings!$A$2,Brian!$O:$O=$C$3,Brian!$N:$N=*,Brian!$AK:$AK>=$F$3,Brian!$AK:$AK<$G$3)),0)

I know I could simply delete the condition "Brian!$N:$N=" to achieve a wildcard selection, but I can't in my case because A7, $C$3, and $F$3, are all values that the user selects from drop-downs options. What I'm trying achieve is the formula filtering when the user selects drop-down criteria which includes blanks. For example, criteria could be:

$C$3 = "America"
A7 = * or (blank)
$F$3 = "Q4"

Right now I can't figure out how to allow A7 be blank and the formula still function. Here's an example of what I'm trying to accomplish:
-----------------------------------------------------------------------------------------------
in the following I have presented a solution to the problem mentioned in Jacob's spreadsheet for which he has provided the link in his question