Forum Discussion
Solved
array or no array
I was told to use an array function for this situation: Spendings 2020 Category Product USA UK GER NED Category 3 Shoes 561 1231 1481 1042 Category 4 Pants 801 599 1223 ...
Which version of Excel do you have? If you have Microsoft 365 or Office 2021, it doesn't matter - Excel will treat a formula as an array formula automatically when appropriate, without you having to confirm it with Ctrl+Shift+Enter.
A more effective formula would be
=SUMIFS(A3:A11;H4;C3:F11)
This is not an array formula in any version of Excel.
I'm indeed using the 365 version. So in the earlier versions this didn't work? I guess that answers my question, thanks!
gerwin024 In versions of Excel before Microsft 365/Office 2021, array formulas had to be confirmed with Ctrl+Shift+Enter, otherwise they would return either an error value or an incorrect result. In the latest versions, Ctrl+Shift+Enter is not needed anymore.