SUMIFS - Multiple Criteria within a single critera

Hey all,

Running into issues with sumifs on multiple criteria.

Trying to do it all within one single sumifs formula instead of having a single sumifs formula for each different string of the criteria.

For example, assume column A has the status of a project (open, closed, in progress), column B has the product type (product1, product2), and column C has the office assigned to (New York, California) and column D is total revenue.

This formula does not work for, and I do not understand why...=sum(sumifs($D:$D,$A:$A,{"Open","Closed","In Progress"},$B:$B,{"product1","product2"},$C:$C,{"New York","California"}))...anything I am missing here as to why it wouldn't be working?

Welcome to the forum.

Untested, but you could try this:

=SUM(SUMIFS($D:$D,$A:$A,{"Open","Closed","In Progress"},$B:$B,{"product1","product2"},$C:$C,{"New York","California"}))

you might want to try sum in front of it, like... ...=sum(sumifs($D:$D,$A:$A,{"Open","Closed","In Progress"},$B:$B,{"product1";"product2"},$C:$C,{"New York";"California"}))
you may also have to add semicolons to the second and third sets.

Hello and welcome to the forum.

I can't help thinking that you might be better off using a Pivot Table.

But what permuations are you tring to sum. It's not clear to me. Upload a workbook with some manually calculated results.

{"In Progress","Open","Closed"} and {"Product1";"Product2"} give 2 dimensions array with 3x2 = 6 AND condition

Excel and do only support 2 dimensions so, more or condition need to find other ways.

1. Normal plus
=SUM(SUMIFS('data set'!$D:$D,'data set'!$A:$A,{"In Progress","Open","Closed"},'data set'!$B:$B,{"Product1";"Product2"},'data set'!$C:$C,"New York"))
+SUM(SUMIFS('data set'!$D:$D,'data set'!$A:$A,{"In Progress","Open","Closed"},'data set'!$B:$B,{"Product1";"Product2"},'data set'!$C:$C,"California"))

2. Wildcard
=SUM(SUMIFS('data set'!$D:$D,'data set'!$A:$A,{"In Progress","Open","Closed"},'data set'!$B:$B,"Product*",'data set'!$C:$C,{"New York";"California"}))

Comma is for different column, semicolon for different row. to get 2 dimension 1 array must seperate by comma, the other must seperate by semicolon.

Thanks for uploading but you've not manually entered the results you expect to see.