Search code examples
excelarray-formulas

Formula Result in Function Argument Window Different Than in Cell

I have the following array formula:

=MAX(COUNTIF(Sheet2!A$2:Sheet2!A$6,Sheet2!A2:Sheet2!A6))

Which is supposed to give me the count of duplicate entries in a small text column in a worksheet. I've put one duplicate entry to test the formula, and the result I should get is 2. Instead, once I ctrl+enter, the cell shows 0. However, when I click the formula button next to the cell content field, it shows Formula result = 2.

Calculation Options are set to Automatic (this is Excel 2010), and I have no idea why this is happening. Any ideas? Thanks.

Solution

  • Seems it was a keyboard problem with the shift or ctrl buttons stuck (: I clicked on the formula inside the formula argument window, then clicked ctrl+shift+enter and it added the curly brackets for the array formula and the result is now correct and updates correctly when the source data changes.