Sumif function with multiple criteria in Google Sheets

[MarissaB]

Hi all,

I have a dataset on which I applied a SUM.IF function, in combination with an IF function. In the attachment you find how I did this searching for one criteria.
However, now I would like to search for multiple criteria. I would like to see the amount for the sum of 6 different criteria, without copy+paste the formula 6 times.

So, I know I can do:
ALS(C2="Payroll";SOMMEN.ALS(Blad2!D:D;Blad2!A:A;'Calculation sheet'!A2;Blad2!B:B;"Kosten Werkgever"))+ ALS(C2="Payroll";SOMMEN.ALS(Blad2!D:D;Blad2!A:A;'Calculation sheet'!A2;Blad2!B:B;"Loon voor bijdrage Zvw")) etc. But I would like to have ALS(C2="Payroll";SOMMEN.ALS(Blad2!D:D;Blad2!A:A;'Calculation sheet'!A2;Blad2!B:B;"ALL DIFFERENT OPTIONS TO SEARCH FOR"))

Can I solve this with an array function? And If so, how do I do this?

Thanks a lot!!
Marissa

 

[Fluff]

How about
ALS(C2="Payroll";SOM(SOMMEN.ALS(Blad2!D:D;Blad2!A:A;'Calculation sheet'!A2;Blad2!B:B;{"Kosten Werkgever", "Loon voor bijdrage Zvw" })))

 

[MarissaB]

Thanks for your reply. However, this is not working as it gives an error unfortunately.

 

[MarissaB]

Or at least - it is working in Excel but not in google sheets. Do you know if there are differences with this formula between those two?

 

[Fluff]

I know nothing about Sheets so cannot help.
If you are wanting formulae for Sheets, rather than Excel, you should clearly state that in your question & the thread should be posted in the "General Discussion & Other Applications" section of the site.

 

[jasonb75]

it is working in Excel but not in google sheets.

You can't use array constants as sumifs criteria with google sheets, you will have to do it the long way, sumifs(....)+sumifs(....)+sumifs(....)