Advanced Sumifs Formulat

[CM722]

Hi, Can anyone help correct the below formula? I am not getting hte results for all of the data below.



=SUM(SUMIFS('CRD Plan'!F:F,'CRD Plan'!$D:$D,"Assembled Goods",'CRD Plan'!$E:$E,"Wip Job Outs - Planned",'CRD Plan'!$B:$B,{"MFG","CRC"},'CRD Plan'!$BG:$BG,{"EW-6 1.5X2","EH-6 2X3","EJ-10 3X3","ET-28 5X5","EU-16 4X4","EE-8 2X2","ES-16 3X3","ES-20 4X4","ET-32 5X5","EV-48 7X7","ES-24 4X4","EV-36 6X6","ES-12 3X3","EC-26 4X4","EX-10 3X3","EX-12 3X3","EV-40 6X6"}))/1000/4

 

[Marcelo Branco]

To use two array criteria in SUMIFS one should be a vertical array and the other a horizontal array.

See if this works

=SUM(SUMIFS('CRD Plan'!F:F,'CRD Plan'!$D:$D,"Assembled Goods",'CRD Plan'!$E:$E,"Wip Job Outs - Planned",'CRD Plan'!$B:$B,{"MFG";"CRC"},'CRD Plan'!$BG:$BG,{"EW-6 1.5X2","EH-6 2X3","EJ-10 3X3","ET-28 5X5","EU-16 4X4","EE-8 2X2","ES-16 3X3","ES-20 4X4","ET-32 5X5","EV-48 7X7","ES-24 4X4","EV-36 6X6","ES-12 3X3","EC-26 4X4","EX-10 3X3","EX-12 3X3","EV-40 6X6"}))/1000/4

Note that in {"MFG";"CRC"} the separator is a semicolon ; (not a comma) creating a vertical array.

M.

 

[CM722]

Thank you very much!