Formula Help #48

=IF(B3="ALL",SUMPRODUCT(--(G18:G3000=C3),(J18:J3000)),IF(C3="ALL",SUMPRODUCT(--(H18:H3000=B3),(J18:J3000)),IF(B3="ALL",IF(C3="ALL",SUM(J18:J3000)))))

The first two parts of this formula work fine.  If B3= all or if
C3=all, I get the desired result, but if I want both cells to = all
then I get $0.00 for an answer.
0
jimx22 (226)
1/22/2009 1:46:58 AM
excel 39879 articles. 2 followers. Follow

5 Replies
589 Views

Similar Articles

[PageSpeed] 7

HI Jim
Try it like this =IF(AND(B3="All",C3="All"),SUMPRODUCT
HTH
John
"JimS" <jimx22@msn.com> wrote in message 
news:9sjfn4l3od448148pp7mt0mfq3ck7saa8e@4ax.com...
> =IF(B3="ALL",SUMPRODUCT(--(G18:G3000=C3),(J18:J3000)),IF(C3="ALL",SUMPRODUCT(--(H18:H3000=B3),(J18:J3000)),IF(B3="ALL",IF(C3="ALL",SUM(J18:J3000)))))
>
> The first two parts of this formula work fine.  If B3= all or if
> C3=all, I get the desired result, but if I want both cells to = all
> then I get $0.00 for an answer. 

0
johnd1 (109)
1/22/2009 2:32:27 AM
Thanks,  but I still can't get that to work.  (I assume you mean to
put what you wrote at the beginning of the formula.)

On Wed, 21 Jan 2009 21:32:27 -0500, "John" <johnd@newlook.com> wrote:

>HI Jim
>Try it like this =IF(AND(B3="All",C3="All"),SUMPRODUCT
>HTH
>John
>"JimS" <jimx22@msn.com> wrote in message 
>news:9sjfn4l3od448148pp7mt0mfq3ck7saa8e@4ax.com...
>> =IF(B3="ALL",SUMPRODUCT(--(G18:G3000=C3),(J18:J3000)),IF(C3="ALL",SUMPRODUCT(--(H18:H3000=B3),(J18:J3000)),IF(B3="ALL",IF(C3="ALL",SUM(J18:J3000)))))
>>
>> The first two parts of this formula work fine.  If B3= all or if
>> C3=all, I get the desired result, but if I want both cells to = all
>> then I get $0.00 for an answer. 

0
jimx22 (226)
1/22/2009 3:17:34 AM
On Jan 21, 5:46=A0pm, JimS <jim...@msn.com> wrote:
> =3DIF(B3=3D"ALL",SUMPRODUCT(--(G18:G3000=3DC3),(J18:J3000)),
> IF(C3=3D"ALL",SUMPRODUC=ADT(--(H18:H3000=3DB3),(J18:J3000)),
> IF(B3=3D"ALL",IF(C3=3D"ALL",SUM(J18:J3000)))))
>
> The first two parts of this formula work fine. =A0If B3=3D
> all or if C3=3Dall, I get the desired result, but if I
> want both cells to =3D all then I get $0.00 for an answer.

Because when B3=3D"ALL" and C3=3D"ALL", you satisfy the first condition
(just B3=3D"ALL").  You need to test the last condition first, for
example:

=3Dif(and(B3=3D"ALL",C3=3D"ALL"),sum(J18:J3000),
    if(B3=3D"ALL",sumproduct(--(G18:G3000=3DC3),J18:J3000),
    if(C3=3D"ALL",sumproduct(--(H18:H3000=3DB3),J18:J3000),"")))

Note that I added a final case:  "" when neither B3 nor C3 is "ALL".
Fill in whatever you want there.  But you should always cover all
cases.


0
joeu2004 (766)
1/22/2009 3:24:44 AM
This is very good, thank you.  And yes, there is one final condition I
thought I could work out if I got what I needed...but I'm struggling
with it.  This is what I have for that final condition, but it returns
$0.00.

I must be close.  Has it got something to do with the parenthesis on
the final line?

=IF(AND(B3="ALL",C3="ALL"),SUM(J18:J3000),
IF(B3="ALL",SUMPRODUCT(--(G18:G3000=C3),J18:J3000),
IF(C3="ALL",SUMPRODUCT(--(H18:H3000=B3),J18:J3000),
SUMPRODUCT(--(G18:G3003=C3),(H18:H3000=B3),(J18:J3000)))))

Oh wait, I think I got it...I made one change to the final line and it
seems to work:  An asterik after C3 instead of a comma.  (I don't know
why that works, or why I have to make that change, but I'm happy ;-))

SUMPRODUCT(--(G18:G3003=C3)*(H18:H3000=B3),(J18:J3000)))))

Thanks again, appreciate that much.



On Wed, 21 Jan 2009 19:24:44 -0800 (PST), joeu2004
<joeu2004@hotmail.com> wrote:

>On Jan 21, 5:46�pm, JimS <jim...@msn.com> wrote:
>> =IF(B3="ALL",SUMPRODUCT(--(G18:G3000=C3),(J18:J3000)),
>> IF(C3="ALL",SUMPRODUC�T(--(H18:H3000=B3),(J18:J3000)),
>> IF(B3="ALL",IF(C3="ALL",SUM(J18:J3000)))))
>>
>> The first two parts of this formula work fine. �If B3=
>> all or if C3=all, I get the desired result, but if I
>> want both cells to = all then I get $0.00 for an answer.
>
>Because when B3="ALL" and C3="ALL", you satisfy the first condition
>(just B3="ALL").  You need to test the last condition first, for
>example:
>
>=if(and(B3="ALL",C3="ALL"),sum(J18:J3000),
>    if(B3="ALL",sumproduct(--(G18:G3000=C3),J18:J3000),
>    if(C3="ALL",sumproduct(--(H18:H3000=B3),J18:J3000),"")))
>
>Note that I added a final case:  "" when neither B3 nor C3 is "ALL".
>Fill in whatever you want there.  But you should always cover all
>cases.
>

0
jimx22 (226)
1/22/2009 4:34:47 AM
On Jan 21, 8:34=A0pm, JimS <jim...@msn.com> wrote:
> I must be close. =A0Has it got something to do with the parenthesis on
> the final line?
> [....]
> SUMPRODUCT(--(G18:G3003=3DC3),(H18:H3000=3DB3),(J18:J3000)))))
> Oh wait, I think I got it...
> SUMPRODUCT(--(G18:G3003=3DC3)*(H18:H3000=3DB3),(J18:J3000)))))

Just for the record, the only problem with the first form is:  you are
missing "--" before the 2nd term (H18:H3000=3DB3).  And in the second
form, the "--" are redundant.  Finally, learn from example.  You might
notice that I did not put parentheses around the last term
(J18:J3000); they are unnecessary in this context.  No harm, either.
0
joeu2004 (766)
1/22/2009 6:26:47 AM
Reply:

Similar Artilces: