Array formula - doesn't go the distance!

  • Thread starter Thread starter atom
  • Start date Start date
A

atom

{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1,
2,3})*{42,21,11})}

This formula works fine, up until the separated part, then it just does
nothing(I put the spaces in just to clarify the problem area for this
discussion). No matter what order put the arguments in, it only works up
to the same point. Is this just a case of to much info? How do I extend
this formula (I need it to be even longer the above example).

thanks.
 
Hi Atom,
Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER
since it is not an array formula
Bernard
 
Hi,
I tried SUMPRODUCT, and had the same problem at the same point in the
formula. I'm baffled.

Also...Initially, I wasn't sure if it should be an array formula, but either
way it doesn't work!

thanks
 
Are you sure you've got the right number of parentheses?

If I'm counting them right, I would expect your formula to evaluate as the
total of :
{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})
and
((F45=20)*(H45={1,2,3})*{42,21,11})}
 
It doesn't seem like a parentheses problem. It works without error, it just
stops working for the last two arguments (no matter what the arguments are).
 
I believe your last two arguments (when F45=20) is in fact there, you just
don't see them because you placed them into 1 cell.
I believe it's a parentheses problem like Brian pointed out. Try selecting
3 horizontal cells together, and array enter your formula.
Your 2nd and 3rd numbers should appear here when appropriate.
Because it looks like you are doing an "or" function, you may want to break
up the Sum formula into two halves...

=SUM( (F45=10) * (H45={1,2,3}) * {20,10,5},(F45=20) * (H45={1,2,3}) *
{42,21,11})

{Array entered}

HTH.
 
Back
Top