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.
=SUMPRODUCT((A3:A11=H4)*C3:F11)You can try the SUMPRODUCT formula which seems to work in my sheet.