SUMPRODUCT Question

[Guest]

Gurus,

I need a little help with a sumproduct function.

In my worksheet I have two fields that have data validation lists in them
that users will select a value from. This works just fine. The sumproduct
results change with their selections.

What I want to do, but don't know how, is to give a "all" choice when the
fields with the data validation lists are blank. In other words, if the user
doesn't choose an option in the field then they want to see all without a
filter.

My current formula is:
=SUMPRODUCT(
--(Schedule!$A$2:$A$9981=$A$8),
--(Schedule!$I$2:$I$9981=$A$9))

Thanks in advance.

Scott

 

[Bob Phillips]

=SUM(
(IF($A$8="",ROW(Schedule!$A$2:$A$99)^0,ISNUMBER(MATCH(Schedule!$A$2:$A$99,$A$8,0))))*
(IF($A$9="",ROW(Schedule!$I$2:$I$99)^0,ISNUMBER(MATCH(Schedule!$I$2:$I$99,$A$9,0)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Once again Bob, YOU ARE THE MAN!!!!