Help with SUMIFS formula

CM722

New Member
Joined
Jan 13, 2016
Messages
16
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top