Array Forumula Using SUM

  • Thread starter Thread starter Ziad
  • Start date Start date
Z

Ziad

I have the array formula:

=SUM(('#1-All'!$F$2:$F$914="X")*('#1-All'!$E$2:$E$914="Y")*
('#1-All'!$B$2:$B$914="Z"))

This will tell how many records in sheet '#1-ALL' have
column F="X", column E="Y" and column B="Z".

What if I want the number of records in sheet '#1-ALL'
that have column F=any value, column E="Y" and column
B="Z"? This is similar to the first example, but I want
to see where column F is not missing.
 
I know that I can just remove the ('#1-All'!
$F$2:$F$914="X") portion of the formula to get the result,
but I have a combo box that changes "X" to "Y" or "Z" (the
values in column F). I want the option of looking
for "X", "Y", "Z", or ALL.
 
Ziad

One way:

SUM(('#1-All'!$F$2:$F$914<>"")*('#1-All'!$E$2:$E$914="Y")*
('#1-All'!$B$2:$B$914="Z"))

or

SUMPRODUCT(('#1-All'!$F$2:$F$914<>"")*('#1-All'!$E$2:$E$914="Y")*
('#1-All'!$B$2:$B$914="Z"))

to be entered with just <Enter>.
 
If col. F can be any value, simply remove this part from
the array formula:

=SUM(('#1-All'!$E$2:$E$914="Y")*('#1-All'!$B$2:$B$914="Z"))

If you do not want to count blank cells in F but still
count any value, try:

=SUM(('#1-All'!$F$2:$F$914<>"")*('#1-All'!$E$2:$E$914="Y")*
('#1-All'!$B$2:$B$914="Z"))

Array-entered.

HTH
Jason
Atlanta, GA
 
Back
Top