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.

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

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" wrote in message
...
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.