Formula Help

  • Thread starter Thread starter JimS
  • Start date Start date
J

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.
 
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.)
 
=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.
Click to expand...

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.
 
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.
 
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)))))
Click to expand...

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.
 
Back
Top