Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel2000 A working formula has {braces} which disappear. Why?
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
array formula
Ctrl-Shift-Enter |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula entered not working | Excel Discussion (Misc queries) | |||
Formula not working | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
looking for a formula | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |