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. |
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. |
All times are GMT +1. The time now is 10:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com