array or no array

[gerwin024]

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?

[#2 · Accepted 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.

[#3]

@gerwin024 

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

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

[#4]

@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!

[#5]

@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.