Formula Help

[JimS]

=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.

 

[John]

HI Jim
Try it like this =IF(AND(B3="All",C3="All"),SUMPRODUCT
HTH
John

 

[JimS]

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.)

 

[joeu2004]

=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.

 

[JimS]

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.

 

[joeu2004]

I must be close.  Has it got something to do with the parenthesis on
the final line?
[....]
SUMPRODUCT(--(G18:G3003=C3),(H18:H3000=B3),(J18:J3000)))))
Oh wait, I think I got it...
SUMPRODUCT(--(G18:G3003=C3)*(H18:H3000=B3),(J18:J3000)))))

Just for the record, the only problem with the first form is: you are
missing "--" before the 2nd term (H18:H3000=B3). 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.