Excel 2010 not displaying correct formula result

**Dave2U** · 06-04-2012, 02:27 AM

Greetings,

I have an Excel 2010 workbook that uses a "combo-IF" formula to summarize results from another worksheet. Using the same formular, I am able to obtain and display the correct result in 4 cells of the summary worksheet. But I am unable to display the correct result in at least 2 cells. The remaining two cells are null values and have not been tested.

The fx ["Function Arguments"] dialog indicates the correct formula result = 1 while the cell insists on displaying "0". I have attempted changing the formatting, re-entering the formula and "find and replace" the equal sign in the formula. I have noticed that the correctly functioning formulas in the 4 cells have the entire formula (including the "=") surrounded by { and }. If I attempt to manually replicate these braces in the formula in question, it does not allow me to click on the formula checkmark.

Any assistance you can render would be greatly appreciated.

Dave2U

**npamcpp** · 06-04-2012, 02:32 AM

These are array formulae. If you edit the formula, you need to confirm it with Ctrl-Shift-Enter, not just Enter. That will create the {} brackets around the formula and return the result of 1

**vlady** · 06-04-2012, 02:33 AM

go to that cell
the formula are supposed to be array formulas.

click on the formula bar to activate/edit the formula
press - ctrl+shift+enter

you'll then notice the { and } in the formula

those braces are not supposed to be typed manually.

**Spencer101** · 06-04-2012, 02:34 AM

If you have entered some of them with the curly brackets manually, you may wish to go back and edit all of these formulae again, entering with Ctrl-Shft-Enter as stated above, just to make sure they all work properly. Otherwise you may find yourself with one that looks like an array formula but actually isn't.

**zbor** · 06-04-2012, 02:34 AM

You can not manually write {}.
You must enter formula with CSE: ctrl+shift+enter (and not just enter).
Then {} will appear around your formula.

Also, you can use SUMPRODUCT formula then you won't need CSE entering:

Like =SUMPRODUCT(('Active Members 2012'!$G$2:$G$151="New")*('Active Members 2012'!$F$2:$F$151="Regular")) instead of =SUM(('Active Members 2012'!G2:G151="New")*('Active Members 2012'!F2:F151="Regular"))