Forum Discussion
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 | 1053 |
| Category 5 | Shirts | 347 | 123 | 397 | 384 |
| Category 3 | Shoes | 995 | 1014 | 1080 | 1108 |
| Category 4 | Pants | 828 | 1408 | 887 | 12 |
| Category 5 | Shirts | 538 | 1342 | 1071 | 1234 |
| Category 3 | Shoes | 200 | 1049 | 752 | 947 |
| Category 4 | Pants | 990 | 187 | 156 | 298 |
| Category 5 | Shirts | 898 | 294 | 839 | 1437 |
H4 = Category 3
=SUM(IF(A3:A11=H4;C3:F11;0))
Whether I use the function as an array or not, is has the same outcome. Can anybody tell me if there are benefits/differences to use it as an array function or not?
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.
4 Replies
=SUMPRODUCT((A3:A11=H4)*C3:F11)You can try the SUMPRODUCT formula which seems to work in my sheet.
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.
