SOLVED

array or no array

New Contributor

I was told to use an array function for this situation:

 Spendings 2020
CategoryProductUSAUKGERNED
Category 3Shoes561123114811042
Category 4Pants80159912231053
Category 5Shirts347123397384
Category 3Shoes995101410801108
Category 4Pants828140888712
Category 5Shirts538134210711234
Category 3Shoes2001049752947
Category 4Pants990187156298
Category 5Shirts8982948391437

 

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?

4 Replies
best response confirmed by gerwin024 (New Contributor)
Solution

@gerwin024 

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.

@gerwin024 

=SUMPRODUCT((A3:A11=H4)*C3:F11)

You can try the SUMPRODUCT formula which seems to work in my sheet.

@Hans Vogelaar 

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.