# Help with SUMIFS formula

#### CM722

##### New Member
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

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
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
Thank you very much, I will try that!

#### CM722

##### New Member
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

##### MrExcel MVP

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:

Thank you!

Replies
2
Views
164
Replies
3
Views
35
Replies
1
Views
71
Replies
1
Views
101
Replies
10
Views
102