sum(if and wild cards

  • Thread starter Thread starter Scott
  • Start date Start date
S

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?
 
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
 
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
 
Thanks Pete that worked great. Is there also something like the SUMPRODUCT
that would count the number of occurances instead of summing?
 
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
 
=SUMPRODUCT((General!$Q$2:$Q$1000 = $A3)*(General!$R$2:$R$1000 =
$B3)*(General!$S$2:$S$1000 = $C$3))
 
Pete_UK said:
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)
Click to expand...

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.
 
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
 
Back
Top