sum(if and wild cards

[Scott]

I have a user input from 3 selections using data validation lists. I then
find the selection matches in 3 columns and then add numbers from the
corresponding row in another column. I got that to work using

=SUM(IF((General!$Q$2:$Q$1000 = $A3)*(General!$R$2:$R$1000 =
$B3)*(General!$S$2:$S$1000 = $C$3),General!$T$2:$T$1000))

where the user selections are from $A3, $B3, $C3.

Now what I would like to do is not require the user to select from one or
more of the vaildation lists if he/she wants everything for that selection
(something like a wild card in the search criteria). I cant figure out how
to modify the formula above to accomplish this. Any ideas?

 

[Pete_UK]

First of all, you could use a SUMPRODUCT formula rather than the array-
entered SUM(IF( that you are using now:

=SUMPRODUCT((General!$Q$2:$Q$1000 = $A3)*(General!$R$2:$R$1000 =
$B3)*(General!$S$2:$S$1000 = $C$3),General!$T$2:$T$1000))

This does not need to be entered with Ctrl-Shift-Enter, and does the
same job.

Secondly, if you are using lists for your Data Validation, then add
All as the first item in the list - the user can select this (rather
like a filter) and this will act as a wildcard in the following
monster formula:

=IF(AND($A$3="All",$B$3="All",$C$3="All"),SUM(General!$T$2:$T$1000),0)
+IF(AND($A$3="All",$B$3="All",$C$3<>"All"),SUMPRODUCT(--(General!$S
$2:$S$1000=$C$3),General!$T$2:$T$1000),0) +IF(AND($A$3="All",$B
$3<>"All",$C$3="All"),SUMPRODUCT(--(General!$R$2:$R$1000=$B$3),General!
$T$2:$T$1000),0) +IF(AND($A$3<>"All",$B$3="All",$C
$3="All"),SUMPRODUCT(--(General!$Q$2:$Q$1000=$A$3),General!$T$2:$T
$1000),0) +IF(AND($A$3="All",$B$3<>"All",$C
$3<>"All"),SUMPRODUCT((General!$R$2:$R$1000=$B$3)*(General!$S$2:$S
$1000=$C$3),General!$T$2:$T$1000),0) +IF(AND($A$3<>"All",$B$3="All",$C
$3<>"All"),SUMPRODUCT((General!$Q$2:$Q$1000=$A$3)*(General!$S$2:$S
$1000=$C$3),General!$T$2:$T$1000),0) +IF(AND($A$3<>"All",$B$3<>"All",$C
$3="All"),SUMPRODUCT((General!$Q$2:$Q$1000=$A$3)*(General!$R$2:$R$1000=
$B$3),General!$T$2:$T$1000),0) +IF(AND($A$3<>"All",$B$3<>"All",$C
$3<>"All"),SUMPRODUCT((General!$Q$2:$Q$1000=$A$3)*(General!$R$2:$R
$1000=$B$3)*(General!$S$2:$S$1000=$C$3),General!$T$2:$T$1000),0)

Note that this is all one formula - basically, there are 8 IFs, one
for each of the conditions that can arise from A3, B3 or C3 containing
"All" or not.

I'm sure someone will come along with a simplification ...

Hope this helps.

Pete

 

[Pete_UK]

Sorry, that didn't paste as clearly as I was hoping - see if this is
any better:

=IF(AND($A$3="All",$B$3="All",$C$3="All"),SUM(General!$T$2:$T$1000),0)
+IF(AND($A$3="All",$B$3="All",$C$3<>"All"),SUMPRODUCT(
--(General!$S$2:$S$1000=$C$3),General!$T$2:$T$1000),0)
+IF(AND($A$3="All",$B$3<>"All",$C$3="All"),SUMPRODUCT(
--(General!$R$2:$R$1000=$B$3),General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3="All",$C$3="All"),SUMPRODUCT(
--(General!$Q$2:$Q$1000=$A$3),General!$T$2:$T$1000),0)
+IF(AND($A$3="All",$B$3<>"All",$C$3<>"All"),SUMPRODUCT(
(General!$R$2:$R$1000=$B$3)*(General!$S$2:$S$1000=$C$3),
General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3="All",$C$3<>"All"),SUMPRODUCT(
(General!$Q$2:$Q$1000=$A$3)*(General!$S$2:$S$1000=$C$3),
General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3<>"All",$C$3="All"),SUMPRODUCT(
(General!$Q$2:$Q$1000=$A$3)*(General!$R$2:$R$1000=$B$3),
General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3<>"All",$C$3<>"All"),SUMPRODUCT(
(General!$Q$2:$Q$1000=$A$3)*(General!$R$2:$R$1000=$B$3)*
(General!$S$2:$S$1000=$C$3),General!$T$2:$T$1000),0)

I need a coffee now ...

Pete

 

[Scott]

Thanks Pete that worked great. Is there also something like the SUMPRODUCT
that would count the number of occurances instead of summing?

 

[Pete_UK]

Yes, if you wanted to just count the occurrences rather than sum them,
you would omit the term after the comma. In the first example I gave
you this would become:

=SUMPRODUCT((General!$Q$2:$Q$1000 = $A3)*(General!$R$2:$R$1000 =
$B3)*(General!$S$2:$S$1000 = $C$3))

so perhaps you could adapt this in the monster formula I gave you. Use
COUNT instead of SUM in the first IF, and then you could do Find and
Replace on the rest of the cell (highlight it and a blank cell first)
to find:

,General!$T$2:$T$1000

and replace it with nothing.

Hope this helps.

Pete

 

[Don Guillett]

=SUMPRODUCT((General!$Q$2:$Q$1000 = $A3)*(General!$R$2:$R$1000 =
$B3)*(General!$S$2:$S$1000 = $C$3))

 

[Harlan Grove]

Sorry, that didn't paste as clearly as I was hoping - see if this is
any better:

=IF(AND($A$3="All",$B$3="All",$C$3="All"),SUM(General!$T$2:$T$1000),0)
+IF(AND($A$3="All",$B$3="All",$C$3<>"All"),SUMPRODUCT(
   --(General!$S$2:$S$1000=$C$3),General!$T$2:$T$1000),0)
+IF(AND($A$3="All",$B$3<>"All",$C$3="All"),SUMPRODUCT(
   --(General!$R$2:$R$1000=$B$3),General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3="All",$C$3="All"),SUMPRODUCT(
   --(General!$Q$2:$Q$1000=$A$3),General!$T$2:$T$1000),0)
+IF(AND($A$3="All",$B$3<>"All",$C$3<>"All"),SUMPRODUCT(
   (General!$R$2:$R$1000=$B$3)*(General!$S$2:$S$1000=$C$3),
   General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3="All",$C$3<>"All"),SUMPRODUCT(
   (General!$Q$2:$Q$1000=$A$3)*(General!$S$2:$S$1000=$C$3),
   General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3<>"All",$C$3="All"),SUMPRODUCT(
   (General!$Q$2:$Q$1000=$A$3)*(General!$R$2:$R$1000=$B$3),
   General!$T$2:$T$1000),0)
+IF(AND($A$3<>"All",$B$3<>"All",$C$3<>"All"),SUMPRODUCT(
   (General!$Q$2:$Q$1000=$A$3)*(General!$R$2:$R$1000=$B$3)*
   (General!$S$2:$S$1000=$C$3),General!$T$2:$T$1000),0)

Sometimes brute force isn't the answer.

=SUMPRODUCT(--((($A$3="All")+(General!$Q$2:$Q$1000=$A$3))
*(($B$3="All")+(General!$R$2:$R$1000=$B$3))
*(($C$3="All")+(General!$S$2:$S$1000=$C$3))>0),General!$T$2:$T$1000)

to SUM conditionally,

=SUMPRODUCT(--((($A$3="All")+(General!$Q$2:$Q$1000=$A$3))
*(($B$3="All")+(General!$R$2:$R$1000=$B$3))
*(($C$3="All")+(General!$S$2:$S$1000=$C$3))>0))

to count conditionally.

 

[Pete_UK]

Harlan,

thanks for that.

I tried to do something along those lines initially but couldn't get
it to come out right, so then moved on to the brute force method. <g>

I'm grateful that you picked it up.

Pete