Excel formulas with curly brackets not updating

[_delp_]

This is in Excel 365 and also Excel 2016.
I've my excel data and I've created a formula. This formula works just fine - to start with.

The actual formula perhaps doesn't matter, but this is it -> 

=SUM(IF((C2:C1142=0)*(B2:B1142=4),A2:A1142))

What it's doing is adding up the contents of column A (from matching rows), if the contents of column C is 0 and column B is 4.  This works fine.

This issue is if I try to update that cell with the formula. It always returns 0, even if I for example enlarge the data set to use C3, B3, A3 rather than C2, B2, A2 in the example. 

What I've noticed is that calculation options are set to automatic.

If I click into the cell with the formula I can see it in the editing area, but it has { } around it.

If I then click into that editing area it changes to 

which is what I'd expect. 

Then I edit it (2 to 3's), the cell with the formula changes from it's value (say 50) to 0 which is wrong and then if I click in the cell again to see the formula the { } are gone.

So I'm not sure if it's seeing it as a formula or not.

The updated cell does have a green triangle in the corner now as well, which seems to suggest an error, but what, I'm not sure as it's the same formula but removing a row of data.

Any advice?

[#2 · Accepted solution]

@_delp_ 

Confirm the formula with Ctrl+Shift+Enter each time you edit it.

[#3]

@Hans Vogelaar That's great to know that shortcut, but why?  Why do I have to do that? Why doesn't the green tick do whatever that shortcut does?

thanks :thumbs_up:

[#4]

@_delp_ 

The curly brackets { } indicate that the formula is a so-called array formula. See Array formula for more info.

The curly brackets are created by pressing Ctrl+Shift+Enter, you should not type them yourself.

Remark: in Excel in Microsoft 365 and in Office 2021, it is no longer necessary to do that; all formulas are automatically evaluated as array formulas.

[#5]

@_delp_ 

Something else I used to do with legacy versions of Excel (mine was 2010) was to enter the formula as the 'Refers to' value of a defined Name.  Excel always did complete the calculation as a defined name whereas the same formula on the grid worked with single values extracted from the input arrays.  CSE was the cumbersome way of telling the Excel worksheet formula that it was to use the entire arrays rather than performing the 'implicit intersection' step first.