SUMPRODUCT Question  
Author Message
yosseeppii





PostPosted: 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





PostPosted: 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 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)



> 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





PostPosted: Tue Sep 25 11:06:05 PDT 2007 Top

worksheet functions >> SUMPRODUCT Question Once again Bob, YOU ARE THE MAN!!!!