Excel2000 A working formula has {braces} which disappear. Why?

[SpudHutton]

I have a worksheet with a working formula that I want to use elsewhere.
But when I click on it and click on the formula bar the braces disappear.
If I type it in with or without braces I get #VALUE! and need to Edit | Undo
Typing.
How can I enter a formula like this?
{=SUM(IF($C$4:$C$34="EA",$D$4:$D$34,0)) }
It adds the values between D4 and D34, but only for those rows where the
value in column C is EA.
I realise that DSUM does something similar, but I don't want the criteria
elsewhere on the sheet, just in the formula.
I'm using Excel 2000 9.0.6926 SP-3.

[Duke Carey]

Those brackets indicate an array formula, which is typed like a regular
formula but then committed by pressing Ctrl-Shift-Enter. When done
correctly, Excel adds the brackets around the formula text that you've typed

Therefore, to use this this formula elsewhere, copy & paste it as you
ordinarily would, then, after editing it to reflect the desired ranges,
simply press Ctrl-Shift-Enter

"SpudHutton" wrote:

> I have a worksheet with a working formula that I want to use elsewhere.
> But when I click on it and click on the formula bar the braces disappear.
> If I type it in with or without braces I get #VALUE! and need to Edit | Undo
> Typing.
> How can I enter a formula like this?
> {=SUM(IF($C$4:$C$34="EA",$D$4:$D$34,0)) }
> It adds the values between D4 and D34, but only for those rows where the
> value in column C is EA.
> I realise that DSUM does something similar, but I don't want the criteria
> elsewhere on the sheet, just in the formula.
> I'm using Excel 2000 9.0.6926 SP-3.

[º¼ÖÝÈË]

array formula
Ctrl-Shift-Enter

[Bob Phillips]

For just one condition

=SUMIF($C$4:$C$33,"EA",$D$4:$D$34)

which is not an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SpudHutton" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet with a working formula that I want to use elsewhere.
> But when I click on it and click on the formula bar the braces disappear.
> If I type it in with or without braces I get #VALUE! and need to Edit |
Undo
> Typing.
> How can I enter a formula like this?
> {=SUM(IF($C$4:$C$34="EA",$D$4:$D$34,0)) }
> It adds the values between D4 and D34, but only for those rows where the
> value in column C is EA.
> I realise that DSUM does something similar, but I don't want the criteria
> elsewhere on the sheet, just in the formula.
> I'm using Excel 2000 9.0.6926 SP-3.