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

Hi,

Change the comma to a semi-colon in your array: {"Non Billable";"Central Budget"}

Effectively you need to transpose this range (TRANSPOSE({"Non Billable","Central Budget"}) would equally work, though no need to call an extra function).

Regards

If you do not transpose one array your formula will calculate:
=SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,"Non Billable")
+SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,"Central Budget")
/1000

If you transpose, calculation will be:
=SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,"Non Billable")
+SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,"Non Billable")
+SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,"Central Budget")
+SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,"Central Budget")
/1000

Perfect, thank you both very much.

Not sure I quite understand why this works though, is it because I am effectively multiplying matrices together?

It is similar, yes - because arrays are different shapes, every combination must be calculated.

I see, thank you for the help guys, that was a real head-scratcher