SUMIFS with Multiple Criteria not working

[sharon9999]

Hi

Please can you help.....

I am trying to sum multiple criteria with 3 different columns on another sheet.
My formula is =SUM(SUMIFS(Report!G:G,Report!G:G,"<0",Report!D:D,"BAC",Report!F:F,"Cancelled ",Report!F:F,"Required",Report!F:F,"Moved"))
I want it to sum column G for negative values, with the BAC criteria only in column D and return any negative values associated to Cancelled, Required and Moved only.

Hope this makes sense

Thanks in Advance.

SC

 

[FormR]

My formula is =SUM(SUMIFS(Report!G:G,Report!G:G,"<0",Report!D:D,"BAC",Report!F:F,"Cancelled ",Report!F:F,"Required",Report!F:F,"Moved"))

Hi, you could try like this instead:

=SUM(SUMIFS(Report!G:G,Report!G:G,"<0",Report!D:D,"BAC",Report!F:F,{"Cancelled","Required","Moved"}))

 

[sharon9999]

That works!
Thank you so much for your quick response

 

[sharon9999]

does this work with COUNTIFS, do you know?

I want the same but to count the volume, not sum?

 

[FormR]

does this work with COUNTIFS, do you know?

Hi, yes - it's the exactly the same principle. When converting your SUMIFS() to COUNTIFS() you obviously remove the sum_range argument.

 

[npanag]

Hi,
The formula is excellent:
=SUM(SUMIFS(Report!G:G,Report!G:G,"<0",Report!D:D,"BAC",Report!F:F,{"Cancelled","Required","Moved"}))
Question:
Can I have cells inside the array, for example something like {D1, D2, D3}?
Thank you

 

[FormR]

Can I have cells inside the array, for example something like {D1, D2, D3}?

Hi, yes - you can try like this, for example:

=SUMPRODUCT(SUMIFS(Report!G:G,Report!G:G,"<0",Report!D:D,"BAC",Report!F:F,D1:D3))

 

[npanag]

OK, in my example I have a list with the following columns: Product, Customer,..., Total.

I have put a table in the list named Sales. At the right of the table I have two Products, with the names Product_Item1 and Product_Item2 and below them I have one Customer with the name Customer_Item.
The sumproduct alone is the following

=SUMPRODUCT(Sales[Total], --((Sales[Product]=Product_Item1)+(Sales[Product]=Product_Item2)), --(Sales[Customer]=Customer_Item))
and gives in my example 159318.4
The Sumifs like your example SUMPRODUCT(SUMIFS(Sales[Total],Sales[Product],M2:N2,Sales[Customer],Customer_Item)) gives correctly the same number, 159318.4

But if I replace the Sumproduct with Sum, =SUM(SUMIFS(Sales[Total],Sales[Product],M2:N2,Sales[Customer],Customer_Item)) then it give me the wrong 36247.05.

I thought the the combination sum(sumifs..) would give the same number.

I would upload my file (that is from a Lynda seminar) but (shame on me) I cannot find the button to upload files.

 

[FormR]

Hi, I'm not sure if you are still looking for a solution or not.

I would upload my file (that is from a Lynda seminar) but (shame on me) I cannot find the button to upload files.

You can't upload files, but you can post example data using one of the tools in post # 2 here:

https://www.mrexcel.com/forum/about-board/508133-attachments.html

if I replace the Sumproduct with Sum, =SUM(SUMIFS(Sales[Total],Sales[Product],M2:N2,Sales[Customer],Customer_Item)) then it give me the wrong 36247.05.

If you want to use SUM() you would need to commit the formula with Control+Shift+Enter.

 

[npanag]

Yes, you are right. It worked correctly with Control+Shift+Enter.

Thank you