
Author 
Message 
yosseeppii

Posted: Tue Sep 25 10:52:06 PDT 2007 
Top 
worksheet functions >> SUMPRODUCT Question
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
Excel37





Bob

Posted: Tue Sep 25 10:52:06 PDT 2007 
Top 
worksheet functions >> SUMPRODUCT Question
=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 CtrlShiftEnter, 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 arrayentered.

HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
> 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





porter444

Posted: Tue Sep 25 11:06:05 PDT 2007 
Top 
worksheet functions >> SUMPRODUCT Question
Once again Bob, YOU ARE THE MAN!!!!






