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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

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:

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,397
Members
409,870
Latest member
Well59
Top