Question about SUMIFS()

It depends on what you're trying to achieve...

If for example you're trying to count the rows where column A is greater than 100 and column B is either X or Y then you could use something like:

=SUM(COUNTIFS(A:A,">100",B:B,{"Y","X"}))

This is checking for more than one value in column B.

Does that help with what you're trying to do?

BSB.

Actually the OP is right to a certain extent in what he has heard, though of course, as BadlySpelledBuoy says, it would be useful to know what is actually being attempted here.

What you have heard sounds like a (rather simplistic) attempt at explaining how to structure queries using COUNTIFS/SUMIFS when you have multiple "OR" choices for your criteria for two of your criteria ranges.

BadlySpelledBuoy gave you one possible syntax where one criterion has several "OR" choices (actually there were just two, though it could quite feasibly have been more). Pete_UK gave you something slightly different, that is a case where you have several "AND" criteria for a single criteria range.

Let me give you an example to clarify the differences, and hopefully explain some of the syntax along the way (and why you may have heard that "criteria_ranges must be in different rows or columns").

Supposing we have the following:

	A	B
1	Field1	Field2
2	Cat	Red
3	Dog	Red
4	Mouse	Green
5	Dog	Red
6	Rabbit	Yellow
7	Cat	Blue
8	Dog	Green
9	Dog	Orange
10	Cat	Blue

then we can generate quite simple results, such as:

=COUNTIFS(A2:A10,"Dog",B2:B10,"Red")

which gives 2, the number of rows satisfying both criteria of having "Dog" in Field1 and "Red" in the corresponding row in Field2.

Similarly:

=COUNTIFS(A2:A10,"Cat",B2:B10,"Red")

which is 1, and equates to the number of rows satisfying both criteria of having "Cat" in Field1 and "Red" in the corresponding row in Field2.

All straightforward stuff. Now:

=COUNTIFS(A2:A10,"Dog",B2:B10,"Red")+COUNTIFS(A2:A10,"Cat",B2:B10,"Red")

which is a simple addition of the above two formulas (and so naturally returns 3), is obviously giving the number of rows satisfying both criteria of having either "Dog" or "Cat" in Field1 and "Red" in the corresponding row in Field2.

A shorter, niftier way to achieve exactly the same thing is the following syntax:

=SUM(COUNTIFS(A2:A10,{"Dog","Cat"},B2:B10,"Red"))

which can also be written:

=SUM(COUNTIFS(A2:A10,{"Dog";"Cat"},B2:B10,"Red"))

(where all I've done is change the comma in the first array to a semi-colon.)

Things get a little more complex when we add a second "OR" criterion to Field2 as well:

=SUM(COUNTIFS(A2:A10,{"Dog","Cat"},B2:B10,{"Red","Blue"}))

or:

=SUM(COUNTIFS(A2:A10,{"Dog";"Cat"},B2:B10,{"Red";"Blue"}))

giving - and this is important - the number of rows satisfying both criteria of having either "Dog" in Field1 and "Red" in the corresponding row in Field2 or "Cat" in Field1 and "Blue" in the corresponding row in Field2.

From the table you can see that rows 3 ("Dog" and "Red"), 5 ("Dog" and "Red"), 7 ("Cat" and "Blue") and 10 ("Cat" and "Blue") meet one of these criteria, and so the result is 4.

The important thing to note in this formula is that each of the elements in the two array constants corresponds with the equivalent element in the other: "Dog" with "Red" and "Cat" with "Blue". The formula says nothing about the possibility of "Dog" in Field1 and "Blue" in Field2 being an option to consider, nor so for "Cat" in Field1 and "Red" in Field2.

But what if we do want these "cross-elements" to be considered, and counted? How do we get a total corresponding to "all possible ORs", i.e. where Field1 can be either "Dog" or "Cat" and Field2 either "Red" or "Blue", i.e. the 5 results in the table?

And this is where you've probably come across talk of criteria_ranges (actually it should be criteria, not criteria_ranges) being "in different rows or columns", because the correct syntax this time is:

=SUM(COUNTIFS(A2:A10,{"Dog","Cat"},B2:B10,{"Red";"Blue"}))

or, equivalently:

=SUM(COUNTIFS(A2:A10,{"Dog";"Cat"},B2:B10,{"Red","Blue"}))

the point being that one of the array constants is a single-column array, the other a single-row array. (In case you didn't know, in Excel commas and semi-colons represent column- and row-separators respectively.)

By doing this you coerce Excel to form a two-dimensional set of returns of all possible combinations for those two sets of criteria. Effectively, Excel is now "seeing" the calculation as a series of separate calculations, equivalent to filling in the two-dimensional grid below:

	Dog	Cat
Red	2	1
Blue	0	2

and then summing up all the totals for those four possibilities.

Instead of stopping at multiple criteria for just two of the criteria_ranges, some people then go on to attempt adding several OR conditions to a third, or even fourth, not realising that this is beyond Excel's capabilities, the principal reason being that, in order to "construct" a grid such as the above for three array constants, we would effectively have to have a "cuboid" of results, each point in this three-dimensional plane corresponding to the number of rows satisfying those criteria.

And I'm afraid Excel just isn't quite at that dimension yet!

Hope that helps.

Regards