complex function
 

I built a function some months ago, which works perfectly.

The difficulty is that the entire text in the cell is bracketed by "curly
brackets" ( { and } ). When I modify anything in the cell, those brackets go
away and the formula, even if I haven't actually changed anything, no longer
works.

I vaguely recall that there was some special keystroke combination or yoga
position I had to use when saving the changes in the cell, in order to not
disturb the curly brackets, but I cannot recall what they may be.

Here is the content of the cell when it's working, if it helps any:

{=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y",IF(AC$ 4:AC$4000="DM",AE$4:AE$4000,0),0),0))+SUM(IF(AF$4: AF$4000="A",IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM" ,AE$4:AE$4000,0),0),0))}

Thanks for your help!!!

-DJW

Ctrl+Shift+Enter (or CSE) is an Array formula. :)
************
Anne Troy
www.OfficeArticles.com

"GTUGoddess" wrote in message
...
I built a function some months ago, which works perfectly.

The difficulty is that the entire text in the cell is bracketed by "curly
brackets" ( { and } ). When I modify anything in the cell, those brackets
go
away and the formula, even if I haven't actually changed anything, no
longer
works.

I vaguely recall that there was some special keystroke combination or yoga
position I had to use when saving the changes in the cell, in order to not
disturb the curly brackets, but I cannot recall what they may be.

Here is the content of the cell when it's working, if it helps any:

{=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y",IF(AC$ 4:AC$4000="DM",AE$4:AE$4000,0),0),0))+SUM(IF(AF$4: AF$4000="A",IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM" ,AE$4:AE$4000,0),0),0))}

Thanks for your help!!!

-DJW

GTUGoddess wrote:
I built a function some months ago, which works perfectly.

The difficulty is that the entire text in the cell is bracketed by "curly
brackets" ( { and } ). When I modify anything in the cell, those brackets go
away and the formula, even if I haven't actually changed anything, no longer
works.

I vaguely recall that there was some special keystroke combination or yoga
position I had to use when saving the changes in the cell, in order to not
disturb the curly brackets, but I cannot recall what they may be.

Here is the content of the cell when it's working, if it helps any:

{=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y",
IF(AC$4:AC$4000="DM",AE$4:AE$4000,0),0),0))+
SUM(IF(AF$4:AF$4000="A",
IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM",AE$4:AE$400 0,0),0),0))}

Thanks for your help!!!

-DJW

You can avoid 'yoga position' with:

=SUMPRODUCT(--(AF$4:AF$4000="A"),--ISNUMBER(MATCH(X$4:X$4000,{"I","Y"},0)),--(AC$4:AC$4000="DM"),AE$4:AE$4000)

which needs just enter (instead of control+shift+enter your original
formula requires).

Have a look here.

http://www.exceltip.com/st/Array_For..._Side/126.html


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=469030

Anne, Aladin and Flintstone,

Thank you all so much! I think I'll stick with CSE for now, given the time
crunch, but I definitely want to try Aladin's suggestion when I have a few
moments to play with it.

Thanks again!!!!

-DJW (GTUGoddess)

"GTUGoddess" wrote:

I built a function some months ago, which works perfectly.

The difficulty is that the entire text in the cell is bracketed by "curly
brackets" ( { and } ). When I modify anything in the cell, those brackets go
away and the formula, even if I haven't actually changed anything, no longer
works.

I vaguely recall that there was some special keystroke combination or yoga
position I had to use when saving the changes in the cell, in order to not
disturb the curly brackets, but I cannot recall what they may be.

Here is the content of the cell when it's working, if it helps any:

{=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y",IF(AC$ 4:AC$4000="DM",AE$4:AE$4000,0),0),0))+SUM(IF(AF$4: AF$4000="A",IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM" ,AE$4:AE$4000,0),0),0))}

Thanks for your help!!!

-DJW