In the example shown, the formula in F5 is: This array formula provides “total sales” in one cell, without a separate column and formula to multiply quantity times price. The curly braces are a clear indication that the formula was entered with CSE. If the same formula is entered without CSE, the result is incorrect. 

Not all array formulas require CSE

Not all array formulas need to be entered with CSE. Entering a formula with CSE explicitly disables a behavior in Excel called “implicit intersection”, but some functions do this natively. For example, in the example shown, the SUMPRODUCT function can be be used instead of SUM without CSE: The SUMPRODUCT formula returns the same result, but does not require CSE. Author

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.