Sum if function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kishor Bhalerao - 29 May 2008 11:42 GMT

Hi,

I am working on a report in which i have gone through the forumla
{=SUM(IF((('Open Detail'!$B$2:$B$5210="FMS")*(('Open
Detail'!$F$2:$F$5210="IR")+('Open Detail'!$F$2:$F$5210="PR"))*(('Open
Detail'!$O$2:$O$5210<2))),1,0),C13,D13,E13,G13)}. I am not able to understand
what is '{' before the formula gets started. When i click on the formula the
'{' signs does not display. Pls suggest..

Reply to this Message

Bernard Liengme - 29 May 2008 12:06 GMT

The braces {} indicate the formula is an "array formula"
You should select the cell (or sometimes the cellS) for the formula and
rather than press ENTER use CTRL+SHIFT+ENTER
Excel then adds the {} and computes correctly
Visit Chip at www.cpearson.com to learn more

Your formula looks strange. It adds a 1 for every time some ranges have
certain values, then it add the row 13 values. Are you sue you do not want
to add the row 13 values subject to those criteria
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Hi,
>
[quoted text clipped - 6 lines]
> the
> '{' signs does not display. Pls suggest..

Reply to this Message

David Biddulph - 29 May 2008 12:46 GMT

The {} signs presumably suggest that it is an array formula, which needs to
be entered with Control Shift Enter, rather than just Enter.
The Control Shift Enter adds the {} brackets.
Any further edits to the formula similarly need to be entered with C.S.E.
--
David Biddulph

> Hi,
>
[quoted text clipped - 6 lines]
> the
> '{' signs does not display. Pls suggest..

Reply to this Message