Help with SUMIFS formula

[CM722]

Hi,

I am trying to write a formula and I have gotten it to work (below), but I have multiple criteria and was wondering if there was a simpler formula to address what I am trying to do? I have had to add 3 of what are basically the same formula together and when trying to use {brackets} for multiple criteria, I am not getting the intended result.

=SUMIFS('CRD Plan'!F:F,'CRD Plan'!$D:$D,"Assembled Goods",'CRD Plan'!$E:$E,"Wip Job Outs - Planned",'CRD Plan'!$B:$B,"PFG",'CRD Plan'!$BG:$BG,"AA-3")/1000/4+SUMIFS('CRD Plan'!F:F,'CRD Plan'!$D:$D,"Assembled Goods",'CRD Plan'!$E:$E,"Wip Job Outs - Planned",'CRD Plan'!$B:$B,"PFG",'CRD Plan'!$BG:$BG,"AB-3")/1000/4+SUMIFS('CRD Plan'!F:F,'CRD Plan'!$D:$D,"Assembled Goods",'CRD Plan'!$E:$E,"Wip Job Outs - Planned",'CRD Plan'!$B:$B,"PFG",'CRD Plan'!$BG:$BG,"AC-4")/1000/4

Any assistance that can be provided would be great.

 

[BiocideJ]

I believe this is what you are looking for.

=SUM(SUMIFS('CRD Plan'!F:F,'CRD Plan'!$D:$D,"Assembled Goods",'CRD Plan'!$E:$E,"Wip Job Outs - Planned",'CRD Plan'!$B:$B,"PFG",'CRD Plan'!$BG:$BG,{"AA-3","AB-3","AC-4"})/1000/4)

The wrapping SUM is because the usage of brackets around the criteria essentially returns an array of outputs (3 in this case), however, a single cell will only display the a single element of that array.
Using SUM around it says to add all of those elements together and display that value.

 

[CM722]

Thank you very much, I will try that!

 

[CM722]

Hi,

I have been using the suggestion above with great success and now I need help on expanding. Is there a way to make the below formula work with two sets of multiple criteria?

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

 

[Aladin Akyurek]

Hi,

I have been using the suggestion above with great success and now I need help on expanding. Is there a way to make the below formula work with two sets of multiple criteria?

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

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

 

[CM722]

Thank you!

 

[Aladin Akyurek]

Thank you!

You are welcome.