Sum Function sometimes displays incorrect answer

[John Westgate]

I am using Excel 2000 SP3 with (to me) a reasonably big spreadsheet (8.5Mb)
in which a set of functions such as the following worked and still work
properly:
=SUM(IF('JWCL-Current'!$H$4:$H$2966=$S$3,IF('JWCL-Current'!$V$4:$V$2966=$C40,'JWCL-Current'!K$4:K$2966,0),0))

I then copied the set of functions to another location and modified one of
the variables (as below)
=SUM(IF('JWCL-Current'!$H$4:$H$2966=$S$89,IF('JWCL-Current'!$V$4:$V$2966=$C120,'JWCL-Current'!K$4:K$2966,0),0))

The result is a displayed value of 0.00 instead of the correct value.

If I use the Insert, Function command on one of the cells containing the
modified formula, it displays in the dialog box the function as edited and
also shows the correct Formula Result for the cell. However, clicking on OK
in the function dialog box still leaves 0.00 displayed in the cell.

I have noted one difference between the original formula and the modified
one when displayed in the edit box at the top of the Excel window. The
original one is as shown above but is also enclosed in a pair of brace
characters, { and }, (although they do not show when I copy the formula from
the edit box to the above). These {} are missing in the edit box of the
modified formula. And entering them in the modified formula via editing
results in display of the formula in the cell rather than a value.

I would be grateful for advice as to what mistake(s) I may be making.
Thanks.

John

 

[LanceB]

John
Your formula is an array formula, start to edit your formula <F2> and then
use cntrl |shift | enter to enter the formula.

Lance

 

[Ron Rosenfeld]

I have noted one difference between the original formula and the modified
one when displayed in the edit box at the top of the Excel window. The
original one is as shown above but is also enclosed in a pair of brace
characters, { and }, (although they do not show when I copy the formula from
the edit box to the above). These {} are missing in the edit box of the
modified formula. And entering them in the modified formula via editing
results in display of the formula in the cell rather than a value.

The braces are Excel's way of telling you that this is an *array* formula.

To enter an *array* formula, instead of merely hitting <enter> after you copy
the formula, you must hold down <ctrl><shift> while hitting <enter>. XL will
then place the braces around the formula.


--ron

 

[John Westgate]

Many thanks to Lance and Ron for their prompt and informative responses. I
can now get the correct answer for the formula I have been using. ( I also
suspect that I must have known something about the difference between array
formulas and standard ones when I originally created the formulas - but that
was 3 years ago and I had forgotten all about them.)

If either of you have a little more time, I have one more question (the
answer to which I cannot see from a quick check of Excel help re array
formulas). I have got something like 200 of these copied/edited formulas to
convert back to array formulas (created as a result of copying/editing one
and then copying to the remaining cells using
the relative/absolute references you can see in my example formula ). Is
there any simple way of converting such a set at one time (which is stored
in a contiguous range of cells, e.g. E55:K99) either by selecting the lot
and doing some operation or recopying from a successfully edited cell e.g.
E55?
If not, I can without too much harm to my fingers, go thru and
ctrl-shift-enter each one.

Thanks again for your help.

John

 

[John Westgate]

Problem below solved - no extra help needed. Thanks again to Lance & Ron.

John