SUMIFS using arrays as criteria

Hi everybody,

The below formula is working fine:

=SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

but if I try to add in a second criteria for Region, as below:

=SUM(SUMIFS(YTD_GBP,Region,{"Central Budgets","Corporate Services"},Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

I get zeros.

But if I do two separate formulas and add them:

=(SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))+SUM(SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"})))/1000

I get the correct answer.


Does anyone know why this is? Are you not able to have two criteria with arrays in a formula?

Thanks
Rich