"logical sum" problem

[Didier Wiroth]

Hi,
I have to do some "logical sum" calculations. I used the excel add-in to
test it.

It worked. Here is the exact formula the add-in put in the cell:
{=SUM(IF($A$2:$A$5744="A";IF($B$2:$B$5744=4;1;0);0))}
For info: The result is 24

When I click on the cell (without even changing something) and exit the cell
editing, the formula doesn't work anymore, the "brackets {}" disappear.
The formula looks like this now:
=SUM(IF($A$2:$A$5744="A";IF($B$2:$B$5744=4;1;0);0))
The result is 0 now???

My problem is that I have to this about 24x88 times, so would like to do it
by hand without the wizard.
How, as all my test gives 0!?

I have to do every single calculation with the "conditional sum add-in
wizard" to get the correct results.

Please why does it delete these {} when I edit the cell?

I tried to do put it back {=...} but it says formula error!!!

thanks for help!!
didier

 

[JulieD]

Hi Didier

the { } indicate that it is an array formula - to get them back, click on
the formula in the formula bar and use CONTROL & SHIFT & ENTER (not just
enter) to commit the formulas and the { } should come back and it should
work.

Hope this helps
Cheers
JulieD

 

[Dave Peterson]

This is an array formula.

You use ctrl-shift-enter instead of just entering the formula. Excel will add
the curly brackets if you do it correctly.

There is an alternate non-array entered function that you may like:

=sumproduct(--($a$2:$a$5744="a"),--($b$2:$b$5744=4))

(You'd use the semicolon instead of the comma, though.)

=sumproduct() likes to work with numbers.
The -- converts true/falses to 1/0's.

 

[Didier Wiroth]

Many thanks, for your help!!!