COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges 67

In this post I would like to clear up what appears to me to be a rather widespread misunderstanding of how COUNTIFS/SUMIFS operate, in particular when we pass arrays consisting of more than one element as the Criteria to one or even two of the Criteria_Ranges.

This latter technique is used when the criteria in question are to be considered as “OR” criteria, which is not to be confused with cases where we wish the criteria passed to be calculated rather as “AND” critieria.

For example, given the following data:

we can use COUNTIFS in various ways and with various syntaxes to obtain a variety of results. For example, starting with some basics:

`=COUNTIFS(B2:B14,"Male",C2:C14,"Sea lion")`

which gives quite simply the number of rows for which both the entry in column B is “Male” and the entry in the corresponding row in column C is “Sea lion”. And:

`=COUNTIFS(B2:B14,"Female",C2:C14,"Sea lion")`

gives precisely the same but for cases where the entry in column B is “Female”.

Without too much difficulty, we can simply add the above, viz:

`=COUNTIFS(B2:B14,"Male",C2:C14,"Sea lion")+COUNTIFS(B2:B14,"Female",C2:C14,"Sea lion")`

to give, not surprisingly, the number of rows for which the entry in column B is either “Male” or “Female” and the entry in the corresponding row in column C is “Sea lion”.

An alternative, shorthand way of obtaining precisely the same result is:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,"Sea lion"))`

Things get a little more interesting when we apply more than one OR criteria, i.e. to the second criteria_range also:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion","Mite"}))`

and you might be surprised to see that the result for this construction (2) is actually considerably less than that for the previous one (5), even though, with precisely the same three criteria as in the previous formula plus one extra, we might be tempted to imagine that, if anything, it would be greater.

What this formula is actually giving is the number of rows for which either the entry in column B is “Male” and the entry in the corresponding row in column C is “Sea lion” or the entry in column B is “Female” and the entry in the corresponding row in column C is “Mite”.

From the table you can see that only rows 12 and 14 (“Male” and “Sea lion”) satisfy this set of conditions. There are no rows corresponding to “Female” and “Mite”. The 3 “Female”/”Sea lion” pairs, which accounted for more than 50% of the total in the previous formula, here do not contribute a thing.

The important thing to note in this formula is that each of the elements in the two array constants corresponds to the equivalent element in the other: “Male” with “Sea lion” and “Female” with “Mite”. The formula says nothing about the possibility of “Male” in column B and “Mite” in column C being an option to consider, nor so for “Female” in column A and “Sea lion” in column C.

But what if we actually want these “cross-elements” to be considered, and counted? How do we get a total corresponding to “all possible ORs”, i.e. where column B can be either “Male” or “Female” and column C either “Sea lion” or “Mite”, i.e. the 7 results in the table (which you can easily verify)?

Well, it may surprise you to know that all we need do is to make one minute change to the previous formula and we have our result, viz:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"}))`

where all I’ve done is to change the comma in one of the array constants to a semi-colon.

Or, equivalently:

`=SUM(COUNTIFS(B2:B14,{"Male";"Female"},C2:C14,{"Sea lion","Mite"}))`

where all I’ve done this time is to swap the comma and semi-colon in the two array constants.

The point here 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 effectively coerce Excel into forming a “two-dimensional” set of returns of all possible combinations for those two sets of criteria. Essentially, Excel is now “seeing” the calculation as a series of separate calculations, equivalent if you like to us filling in by hand the two-dimensional grid below:

and then summing up the totals for those four possibilities.

The interesting – and slightly worrying – thing that I’ve noticed is just how little understood is the logic behind this syntax. It is quite understandable that students of Excel, upon encountering this technique for the first time, are naturally wont to investigate the possibility of extending this method to scenarios involving multiple “OR” criteria for more than two criteria_ranges.

For example, we have no problem in calculating:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"},D2:D14,"Basketball"))`

that is, the number of rows where column B is either “Male” or “Female”, column C is either “Sea lion” or “Mite” and column D is “Basketball” (answer: 1).

Now let’s see what happens if we attempt to extend the criteria for column D to more than a single entry, e.g.:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"},D2:D14,{"Basketball","Genealogy","Roleplaying"}))`

which, looking at the table, we might well hope to return the five rows as below:

However, the result of the above formula is not 5, but 2.

Perhaps we got our syntax wrong? Perhaps the elements in that new array we’ve just added should be separated by semi-colons, not commas? So you try:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"},D2:D14,{"Basketball";"Genealogy";"Roleplaying"}))`

and are perhaps even more dismayed to see that the result is not only not any better than the last one, but not even the same. In fact, the above formula will return 0.

Why is this happening?

The first thing I would like to state for the record is that, no matter how much juggling about of commas and semi-colons we try, unlike in the previous example we are never (at least not using COUNTIFS; SUMPRODUCT will handle it, but that’s a different story) going to be able to create, for this construction, an expression which returns a total corresponding to “all possible ORs”, i.e. where column B is either “Male” or “Female”, column C either “Sea lion” or “Mite” and column D either “Basketball”, “Genealogy” or “Roleplaying”. Never.

But why?

Well, the basic answer boils down to how this function operates, a question of “dimensionality”, if you like. In effect, in order to “construct” a grid such as the one I gave above for the previous case, in which we laid out the two sets of OR criteria as if they were the x and y axes of a Cartesian plot, each vertex corresponding to a pair of those points being the result for that pair, in this case 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.

Imagine extending our previous two-dimensional grid so that it becomes just such a three-dimensional cuboid, with the “z-axis” now consisting of our three new elements – “Basketball”, “Genealogy”, “Roleplaying” – as points on that axis. Theoretically, we could now populate this 3-D grid with values at each of the 12 (our plot would essentially be a 2x2x3 “cuboid” in space) vertices corresponding to the total satisfying each of the criteria on the corresponding x, y and z axes.

Unfortunately we are restricted to a mere two dimensions, and so such fantasies will remain just that (though this function can operate in a pseudo-three-dimensional fashion, as for example in constructions involving multiple worksheets).

So if neither of the above attempts were of valid syntax, why didn’t they simply return an error? Where do the results which they return (2 and 0) come from?

Let’s take a look at the first attempt again:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"},D2:D14,{"Basketball","Genealogy","Roleplaying"}))`

It’s always useful to go through any formula (especially in cases where it is not giving the expected results) using the Evaluate Formula tool. Doing so here reveals that the above resolves to:

`=SUM({0,1,0;1,0,0})`

which, if we look closely, is evidently an array consisting of two rows and three columns.

But from where has this array been derived?

The key thing to remember is the point I made about “pairing” of elements earlier. When two (or more) arrays are of the same “vector-type”, i.e. either both are single-column arrays or both are single-row arrays, then Excel will pair corresponding conditions from each.

Hence, in the above construction, since the first – {“Male”,”Female”} – and third – {“Basketball”,”Genealogy”,”Roleplaying”} – criteria arrays are both single-row arrays, Excel pairs up the elements, so that, just as we saw before, these pairs will each now form an “AND” condition: the first equating to asking how many rows there are for which the entry in column B is “Male” and the corresponding entry in column D is “Basketball”; the second to asking how many rows there are for which the entry in column B is “Female” and the corresponding entry in column D is “Genealogy”.

Note that the other array in this construction – {“Sea lion”;”Mite”} – which was “transposed” to become a single-column array as was required in the earlier construction, is still a valid syntax here, in the sense that it will allow us to form a 2-dimensional array of results. It’s just that the other array with which we are going to “cross-calculate” this one first needs to be resolved from the other two arrays of the same vector-type.

But what about the third element in that third array – “Roleplaying”? Which is the “corresponding” element in the first array to which that is paired?

The answer is that there isn’t one. However, rather than ignore that criterion altogether, Excel nevertheless goes ahead and constructs the appropriately-sized array to house the expected returns, i.e. the two-row by three-column array that we can see when using Evaluate Formula. In effect, faced with two (or more) arrays of differing dimensions, Excel’s means of resolving this syntactical conflict is to artificially increase the smaller of the two so that it is then of a dimension equal to the larger of the pair.

The fact it that does this, and then proceeds to fill in these newly-created, extra spaces with zeroes, rather than flat-out error, is perhaps not such a bad thing: indeed, there may well be situations where we knowingly accept that one of our arrays is being “re-dimensioned”, even filled with errors, providing that we then manipulate the resulting array as befits our needs.

With this in mind we can now give the precise equivalent expression corresponding to each of the six elements in the array we saw generated above, i.e. {0,1,0;1,0,0}, whose breakdown would look like this (observe my attempt to represent there being no third element to pair with “Roleplaying”):

This time two of our three sets of criteria (those having the same vector-type) were first resolved into pairs before then “cross-calculating” with the third.

Let’s have a look at why our second attempt, in which we tried in vain to transpose that final array, returned zero:

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"},D2:D14,{"Basketball";"Genealogy";"Roleplaying"}))`

Although the overall result is 0, so that we know that all of the contributing calculations to that total are also 0, it’s nevertheless worth looking at the resulting array using Evaluate Formula, if nothing else than so we can verify that its dimensionality is what we would expect:

`=SUM({0,0;0,0;0,0})`

This time we have a 3-row-by-2-column array, again consisting of a total of six entries. And in this case our two arrays which are of the same vector-type are the second – {“Sea lion”;”Mite”}, and third – {“Basketball”;”Genealogy”;”Roleplaying”}, both being single-column arrays.

Hence, as we’ve seen, “pairing” them up will give us “Sea lion”/”Basketball”, “Mite”/”Genealogy” and “???”/”Roleplaying”.

These three pairs are then “cross-calculated” with the two elements in the first array – {“Male”,”Female”} – so that our “2-D grid of returns” would this time look like:

(totals which you may wish to manually verify via filtering the original table) and clearly the sum of six zeroes is zero.

To conclude, although this article is set out principally to discuss the operation and syntax of the SUMIFS/COUNTIFS family of functions, it might be argued that the more important lesson to be (hopefully) learnt is one of a deeper appreciation of the underlying means by which such constructions are calculated.

Indeed, if you were to ask me what I think is the one aspect of Excel which I would say is lacking in, and which hinders the progress of, the average Excel user, I would have to say that it’s this very inability (or unwillingness) to grasp the fundamental nature of how Excel (which, remember, is nothing if not a “two-dimensional” programme) operates at this base level.

There are countless Excellers out there (and some of them of very high status) whose work clearly demonstrates an understanding of the standard formula techniques: they use FREQUENCY with apparent ease; they formulate long, complex array formulas; they memorize (and some of them regurgitate) the old, time-tested solutions (sometimes I think if I see another MIN(FIND({0,1,2,3,4,5,6,7,8,9,0}… construction I’ll scream…) and they are adept at employing the vast majority of Excel functions in a variety of situations.

But you won’t see many MMULTs coming from these same people. And you won’t see many non-standard, innovative manipulations of arrays either (after MMULT, perhaps TRANSPOSE would be top of the list for least-employed – and least-understood – function around). But these two functions share – at heart – the same fundamental aspect: that is, they elevate us, by their very nature, to working with constructions in which we are manipulating two-dimensional arrays. Not those which we can see, as is the case with worksheet ranges, but those which reside only within the depths of Excel’s intermediate calculation-chain, and which are transitory.

And it’s just precisely at this time when an appreciation of the underlying means by which we can manipulate, query and even re-dimension these two-dimensional array constructions is of most importance. Yet how many of us have such an appreciation of those fundamentals? Just why do so few people employ MMULT in their solutions?

Understand how Excel “sees” things and you’ll understand Excel far better!

1. Pingback: Sumifs with or?

2. lakshya says:

Is there any solution to applying this

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"},D2:D14,{"Basketball";"Genealogy";"Roleplaying"}))`
3. @lakshya

Hi and welcome to the site!

Can you clarify what you mean? What exactly do you wish to return a count for?

Regards

4. Iggy says:

Great write up and description. I was able to apply the usage to one of my sheets but had a question.

I’m able to use or criteria in a statement that I manually wrote

`=SUM(COUNTIFS(table1[range1],{"type1";"type2"},...`

I want to be able to use a selector so the users have options:

– type3
– type4
– type 1 or type 2

However, when I use a reference cell to point to the argument for type1 and type2, it doesn’t work.

`=SUM(COUNTIFS(table1[range1],{\$A\$3},...`

A3 points to a data validation consisting of:

type3
type4
{“type1″;”type2”}

Do you have any suggestions?

5. @Iggy

Very kind of you to say so. Thanks!

Re your query, can you clarify precisely what is in the data validation list? Do you really have the third entry as you give?

Regards

6. Chris says:

So how would you return the list of all possible “OR”s for the scenario above where

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion";"Mite"},D2:D14,{"Basketball";"Genealogy";"Roleplaying"}))`

fails?

7. Hi Chris.

In such cases you need to switch to SUMPRODUCT:

`=SUMPRODUCT(0+(ISNUMBER(MATCH(B2:B14,{"Male","Female"},0))),0+(ISNUMBER(MATCH(C2:C14,{"Sea lion","Mite"},0))),0+ISNUMBER(MATCH(D2:D14,{"Basketball","Genealogy","Roleplaying"},0)))`

Regards

8. Daniel S. says:

Thank you for the amazing article. I’m trying to apply it to my scenario. Here is a section of my table:

Col1 || Col 2 || Col 3
0 || 1 || 0
1 || 2 || 3
0 || 0 || 0
3 || 1 || 1

etc.

It is 3 columns, and each column can contain either 0, 1, 2, or 3. I would like to return the count of the number of rows for which any of the columns have the number 1.

Any way you could help out?

Thanks!

9. Khalid NGO says:

Hi XOR LX,
Good Day Sir,

Very interesting article, I didn’t knew much about SUMIFS before, this indeed great help, Thank you.

Just wondering why it needs to be wrapped with SUM?

Regards,
Khalid

10. Hi Khalid.

When you pass an array of values as one or more of the criteria ranges to COUNTIF(S)/SUMIF(S), you are effectively doing the equivalent of performing as many individual versions of those functions, one for each of the criteria.

But of course these individual totals then need to be summed to give an overall result.

For example, the construction:

`=COUNTIF(A1:A10,"A")+COUNTIF(A1:A10,"B")`

can be replaced with the shorter:

`=SUM(COUNTIF(A1:A10,{"A","B"}))`

but we cannot simply use it without the SUM, i.e.:,

`=COUNTIF(A1:A10,{"A","B"})`

because, although this technically returns an array of two values, one for the count of “A” within the range and one for the count of “B”, e.g.:

`{3,5}`

we still need to sum those elements to arrive at a total number of entries which are either “A” or “B”, i.e. 8 (=3+5).

Regards

11. Khalid NGO says:

Hi XOR LX,
Thank you so much.

Everything is clear now.
Thanks for the step by step examples.
Highly appreciated.

Take Care.

12. Andrew says:

Hello.

Is it therefore impossible to count each element of the A1:A6 array if it is greater than each element of the B1:B6 array. e.g. is A1>B1 if yes +1, is A2>B2 if yes +1 etc.

Andrew

13. Hi Andrew.

In such row-for-row comparisons we generally need to switch to SUMPRODUCT:

`=SUMPRODUCT(0+(A1:A6>B1:B6))`

Or, if it is not certain that all of the entries in those ranges are numerical, more rigorously:

`=SUMPRODUCT(0+(A1:A6>B1:B6),0+ISNUMBER(A1:A6),0+ISNUMBER(B1:B6))`

Regards

14. AKB says:

Thanks for the detailed explanation.

I tried the {“Male”,”Female”} formula in google spreadsheets but unfortunately it does not seem to be working.

Doesn’t the Google sheets support this?

If not, is there a way around?

15. @AKB

Hi and welcome to the site!

I’m very sorry but I’m afraid I don’t have much experience of Google Sheets. In fact, the solutions I propose here are not also independently verified for working with that program.

Perhaps someone who has a bit more knowledge of Google Sheets than I will pick up on your comment.

Regards

16. hey man im working on something similar but im a bit stuck. was wondering if you can help me because this is similar.

here is my dilemma:

im trying to do the criteria match with the 2 criterias, however for the second criteria i want the home score to be great than the vis. score. (with the first being the team name at home but ive got that sorted it just keeps coming up the value thing when i try complete the rest, here is what i have so far –

`=COUNTIFS(Table2[HOME TEAM],"stuttgart",Table2,Table2[HOME SCORE]>Table2[VIS. SCORE])`

break that down :

=COUNTIFS first criteria (Table2[HOME TEAM],”stuttgart”

this is fine so far… but when i add the next bit.

Table2,Table2[HOME SCORE]>Table2[VIS. SCORE])

what is it exactly im doing wrong with it lol.

17. @puntingpal

Hi and welcome to the site!

Unfortunately you need to switch to SUMPRODUCT in such cases, viz:

`=SUMPRODUCT(0+(Table2[HOME TEAM]="stuttgart"),0+(Table2[HOME SCORE]>Table2[VIS. SCORE]))`

Regards

18. That’s an awesome reply and that worked a treat the above as I just tried it myself. So in the case that you want to record only when it is equal to the vis. Score what would u put in place of the > ???

19. As in I want to record how many times the two team draw?? And also, if I have like 120 different teams to record and the formula bar is heaps complicated it won’t auto correct an calculate the rest of the teams an I have to go thru an change them individually, is there a quicker way to achieve all this for me??

20. Just change the “>” to an “=” for draws.

Why don’t you put all your teams, e.g. “Stuttgart”, in a list somewhere, e.g. A1:A120, and then use, in B1:

`=SUMPRODUCT(0+(Table2[HOME TEAM]=A1),0+(Table2[HOME SCORE]=Table2[VIS. SCORE]))`

which will give you the number of times Stuttgart drew and, when copied down to B2, B3, etc., will give you the number of times that the teams in A2, A3, etc. drew.

Regards

21. I Hate Excel says:

Hello,

I want to count each cell only once, regardless of how many of the criteria words it contains.

Is this possible? I am using:

`=SUM(COUNTIFS('All Data'!\$F:\$F,A2,'All Data'!\$H:\$H,{"*apples*";"*pears*"}))`

For example, if a cell contains both ‘apples’ and ‘pears’ I want it to only be counted once in the overall total.

22. @I Hate Excel

Perhaps the most straightforward solution is simply to subtract the number of entries for which the column H value contains both “apples” and “pears” from your existing formula, i.e.:

`=SUM(COUNTIFS('All Data'!\$F:\$F,A2,'All Data'!\$H:\$H,{"*apples*";"*pears*"}))-COUNTIFS('All Data'!\$F:\$F,A2,'All Data'!\$H:\$H,"*apples*",'All Data'!\$H:\$H,"*pears*")`

This can also be done via a single construction, viz:

`=SUM(COUNTIFS('All Data'!\$F:\$F,A2,'All Data'!\$H:\$H,{"*apples*";"*pears*";"*apples*"},'All Data'!\$H:\$H,{"<>*pears*";"<>*apples*";"*pears*"}))`

though the latter is arguably less intuitive than the former.

Hope that helps!

Regards

23. Alda says:

This was amazingly thorough and helpful. Thank you!

24. @Alda

Glad to hear it! And you’re very welcome!

25. Jisha says:

Nice article.

I have got a scenario wherein I could not figure how to do it. Heres the sample data.

A || B || C || D
106 || 112 || 112 || 924
197 || 209 || 209 || 171
495 || 527 || 421 || 421
0 || 0 || 0 || 0
665 || 707 || 707 || 707
749 || 797 || 797 || 797
749 || 0 || 0 || 0

I want to get the count of all A which are greater B or C or D. The answer here would be 3 (row 2, row 3 and last row).

I tried the sumproduct, but that would work only if the field of comparison is only two columns. Here I have 3 columns to compare to.

How should I proceed?

26. @Jisha

Hi and welcome to the site!

The simplest solution would be to use an additional column within the worksheet.

For example, using column E for this purpose, and assuming the rows to be considered are from row 1 to row 10, enter this formula in E1:

`=MIN(A1:D1)`

and copy down to E10.

Your required counting formula is then simply:

`=SUMPRODUCT(0+(A1:A10>E1:E10))`

If for whatever reason you are unable (or unwilling) to use an additional column, you would need the slightly more complex:

`=SUMPRODUCT(0+(SUBTOTAL(5,OFFSET(A1:D1,ROW(A1:D10)-MIN(ROW(A1:D10)),))<>A1:A10))`

Hope that helps!

Regards

27. Jisha says:

Thank you very much.. I do prefer the second formula, but unfortunately could not decipher it. 😦

28. I’ll break it down for you 🙂

If we use a 5 as the first parameter within SUBTOTAL, we instruct that function to return the minimum from a given range. In fact, we can actually pass more than one range to SUBTOTAL and it will return the minimum from each of those ranges.

Basically, we are going to determine the minimum from each of the ranges:

A1:D1
A2:D2
A3:D3

A10:D10

and then compare each of these minimums with the corresponding row entry from column A.

In order to create an ‘array of ranges’ to pass to SUBTOTAL, we must here use OFFSET. And we start with passing the range A1:D1 as the first parameter to that function, and then ‘offsetting’ it by a number of rows of our choosing.

Just as an illustration, something like:

`OFFSET(A1:D1,1,0)`

would return a reference to the range:

A2:D2

since we have ‘offset’ the range A1:D1 by a single row.

Similarly:

`OFFSET(A1:D1,3,2)`

would return a reference to the range:

C4:F4

since this time we have ‘offset’ the range A1:D1 by three rows (downwards) and two columns (to the right).

However, we are not restricted to passing just single values to the rows and cols parameters of OFFSET; we can also pass multiple values (arrays) all at once.

This is what I did here, since this part:

`ROW(A1:D10)-MIN(ROW(A1:D10))`

which I am passing as OFFSET’s rows parameter, generates not just a single value, but many. Specifically, the above resolves to the array of values:

`{0;1;2;3;4;5;6;7;8;9}`

And so the construction:

`OFFSET(A1:D1,ROW(A1:D10)-MIN(ROW(A1:D10)),)`

is here generating an ‘array’ of range references, equivalent to offsetting the range A1:D1 by 0, 1, 2, …, 9 rows. As such, the result will look like:

`{A1:D1;A2:D2;A3:D3;A4:D4;A5:D5;A6:D6;A7:D7;A8:D8;A9:D9;A10:D10}`

We then ask SUBTOTAL, using 5 as its first parameter, to tell us the minimum value from each of these ranges, so that:

`SUBTOTAL(5,OFFSET(A1:D1,ROW(A1:D10)-MIN(ROW(A1:D10)),))`

would give us (assuming some random numbers in those ranges):

`{95;49;300;1;71;383;99;35;209;339}`

We then finally compare these values to those in column A, as required.

Hope that helps!

29. Jisha says:

Thank you very much for the clear explanation. 🙂
Wondering if it needs to be this complicated.. 😀

30. Hi,

I seem to be stuck on a formula and was wondering if you might be able to help.

I’m using countifs to filter multiple criteria ( 5 columns) to get my results. I am ok up to this point i get the count i am looking for. however, I have duplicate data in column F and need the count of only the unique occurrence. Is there a way to count teh value in the column only once after the other perimeters have been established?

Criteria range has been setup in name manager

example

Column_C =

`=OFFSET(data!\$C\$1,0,0,COUNTA(data!\$C:\$C),1)`

here is my formula

`=COUNTIFS(Column_C,A33,Column_E,A34,Column_I,""&D34,Column_V,""&1)`
31. @Leo

Hi and welcome to the site!

You say that there are five columns to consider, though I can’t see any reference to a column F in your formula. Can you clarify what the criterion for this column is?

Regards

32. Hi, thanks for the reply.

Column F i left out because i can’t seem to figure out.
also I think i left out column T out, it should have been

`=COUNTIFS(Column_C,A33,Column_E,A34,Column_I,""&D34,Column_V,""&1)`

with the above formula I get a count of remaining lines, and its correct, but i would like to take it a step further and get count of unique employee ID from column F

Here’s a breakdown

Column C has multiple division number: 1018001,1018002,1018003

Column E pay area: CE,CL,SM

Column F employee numbers will have duplicates: 110215, 243979, 77132

Column I work days: 5/1/16, 5/2/16, 5/3/16

Column T day time was reported

Column V day time was approved

so an example of what i’m trying to do

get a count when C = 1018001, E=CE, I <5/24/16, T 5/30/16
after all above filters get count of unique employee number from F

33. In that case you need to use an array formula** (see instructions at the foot of this comment), i.e.:

`=SUM(IF(FREQUENCY(IF(Column_C=Column_C_Your_Value,IF(Column_E=Column_E_Your_Value,IF(Column_I=Column_I_Your_Value,IF(Column_T=Column_T_Your_Value,IF(Column_V=Column_V_Your_Value,IF(Column_F<>"",MATCH(Column_F,Column_F,0))))))),ROW(Column_F)-MIN(ROW(Column_F))+1),1))`

I’m afraid I couldn’t work out quite what your criteria were supposed to be, so I’ve just used generic names, e.g. Column_T_Your_Value, which obviously you should replace as required (including the equals signs where appropriate, e.g. to <, >, etc.).

Unlike with e.g. COUNTIF(S)/SUMIF(S), with such a formula it’s extremely important to keep the ranges passed to a minimum, or else you’ll start to notice Excel struggling to calculate. Looks like you’ve made a good start to this effect by defining your ranges. Just make sure that all these defined ranges, for columns C, I, T, etc., are precisely the same size.

Hope that helps!

**Array formulas are not entered in the same way as ‘standard’ formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you’ve done it correctly, you’ll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

34. Thank you for all your help

i had actually started exploring sum/frequency as a means to accomplish what i wanted, I just had a different syntax.

Thanks again!!

35. You’re more than welcome!

Cheers!

36. I must concur that this is an excellent article. I especially like the theoretical discussion at the end, even though I have no idea of what MMULT does or is.

37. @Job

Many thanks for your kind words. 🙂

I may create a post specifically for MMULT here at some point in the near future – it’s a function which seems to remain a bit of a mystery to many Excel users!

38. San New to Excel says:

Great Article.

Is there anyway i can use the or against a cell reference?

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{"Sea lion","Mite"}))`

So the above becomes

`=SUM(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,{" F1","F2"}))`

where F1 can and F2 have the information of the pet, which means its more flexible and can be quickly changed.

39. @San New to Excel

Re your query, the answer is yes, though when the criteria are not static, in-formula values, but rather contained within actual worksheet ranges, then we must switch from SUM to SUMPRODUCT in order to give the necessary coercion, i.e.:

`=SUMPRODUCT(COUNTIFS(B2:B14,{"Male","Female"},C2:C14,F1:F2))`

By the way, I took it from the example you gave (and also assuming you read the full article), in which both criteria arrays are of the same vector displacement (i.e. not orthogonal to each other), that you are interested in obtaining a count for just the two combinations Male/Sea Lion and Female/Mite, and not all four, i.e. Male/Sea Lion, Male/Mite, Female/Mite and Female/Sea Lion.

Regards

40. Tom Prouc says:

Hello

I read with big interest your page and comments but still unable to adapt to my search and to write the correct one for

In the table bellow I try to solve the following request: to find by month the number of PO with backorder and the number of PO shipped partially

PO n° Ordered Shipped Bakcorder Order Date Delivery Date
120250 3 2 1 8/2/2016 8/10/2016
120783 2 2 8/3/2016 8/10/2016
120783 1 0 1 8/3/2016
121997 5 5 8/8/2016 8/12/2016
122001 2 2 8/15/2016 8/22/2016
122001 1 0 1 8/15/2016
122005 3 3 8/29/2016 9/5/2016
122005 2 2 8/29/2016 9/5/2016
122005 4 4 8/29/2016 9/5/2016
122020 2 2 9/5/2016 9/12/2016
122058 8 2 6 9/10/2016 9/12/2016
122082 2 2 9/12/2016 9/25/2016
122095 8 2 6 9/21/2016 10/5/2016
122095 8 1 7 9/21/2016 10/5/2016
122095 3 3 9/21/2016 10/5/2016
122095 1 1 9/21/2016 10/5/2016
122297 10 2 8 10/2/2016 10/5/2016
122297 1 1 10/2/2016 10/5/2016

Result A)
Lines Count August September October
Lines order 9 7 2
lines shipped 4 6 6
Lines backorder 3 3 1

for lines order I used the following formula

`=COUNT(IF(MONTH(E2:E19)=MONTH(B22),E2:E19))`

for lines shipped

`=COUNT(IF(MONTH(F2:F19)=MONTH(B22),F2:F19))`

Lines backorder

`=COUNTIFS(\$D2:\$D19,">0",\$E2:\$E19,">="&B31,\$E2:\$E19,<="&EOMONTH(B22,0))`

Result B)

PO count August September October
PO 5 4 1
PO shipped 4 4 2
O bakordered Formula to find
PO shipped full Formula to find

for PO count I used the following formula

PO:

`=COUNT(1/FREQUENCY(IF(MONTH(\$E\$2:\$E\$19)=MONTH(B29),\$A\$2:\$A\$19),\$A\$2:\$A\$19))`

PO shipped:

`=COUNT(1/FREQUENCY(IF(MONTH(\$F\$2:\$F\$19)=MONTH(B29),\$A\$2:\$A\$19),\$A\$2:\$A\$19))`

Can you help me? Thank you in adavance

41. Hi Tom,

I’d gladly help you here, but could I just ask you to first confirm which of the formulas you’ve posted does not give the desired results? Also, can you confirm what the expected results for that dataset should be in each case?

Regards

42. Tom Prouc says:

Hello

Rereading my post I realize I was not clear giving information.

All formulas in result A (from previous post) work well. I can obtain the number of lines ordered / shipped / backorder on a specific period
Formulas in result B give info to obtain the number of PO and PO shipped on a period working find too.

The complexity is that I try to obtain the number of orders forms (col A is you copy table in excel sheet) having at least one backorder line (col D) over a given period (col E) is calculated over a full month August, September etc..

Attention, the order form (PO) may appear several times, the 120783 appears 2 times but must be counted only as 1, the 122005 appears 3 time buts must be counted as 1 and soon

The number to obtain for PO backordered is 3 for august, 2 for September, 1 for October

Thank you

43. So unless I’m missing something obvious you could simply add an additional clause to one of your existing formulas, viz:

`=COUNT(1/FREQUENCY(IF(MONTH(\$E\$2:\$E\$19)=MONTH(B29),IF(\$D\$2:\$D\$19<>"",\$A\$2:\$A\$19)),\$A\$2:\$A\$19))`

Regards

44. Tom Prouc says:

The formula works well. Like always, focusing on “countifs” formula, forgetting to go back to the basis, using the “If” formula. As was saying one of my past bosses: why make it easy when you can do it complicated…

Thank you

45. Wise words indeed! And you’re welcome!

Cheers

46. Casey says:

Hello! You had helped someone above (@I HATE EXCEL) with a similar scenario but I cant figure out how to make it work for my case. I have a number of possible “tags” that could be in a cell (about 30 in total). Many of them fall under the same category so I need to be able to count any that match my criteria (#TAG1 or #TAG2 or #TAG3) but only count each row once. An example might be the cell might contain someone favorite foods as “apples, oranges” and I want to count each response only once under the category of “Fruit”

`=SUM(COUNTIFS('Sheet1'!\$F:\$F,\$A6,'Sheet1'!\$J:\$J,{"*#TAG1*";"*#TAG2*";"*#TAG3*"}))`

The idea of subtracting out those cells containing both works when the tag list is short but when I have 10 possible tags (lemons, pears, apples, oranges, grapes, etc.) I am trying to account for, I would need to subtract out every possible combination of the grouped tags.

Any thoughts on how else I might count these cells only once in this scenario?

47. @Casey

Yes, you need a slightly different set-up in such cases. See my post here, for example:

You would need something like the following array formula**, where I have assumed that the list of criteria (#TAG1, #TAG2 and #TAG3) are in cells E1;E3:

`=SUM(IF(Sheet1!\$F1:\$F20=\$A6,IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(E1:E3),Sheet1!\$J1:\$J20)),ROW(E1:E3)^0)>0,1)))`

Note also that, since we no longer have the option of using COUNTIFS, we must be careful to restrict the range being passed (and certainly referencing entire columns is no longer an option). Hence my choice of an upper row reference of 20 here, which obviously you can change, though be sure to keep it as small as possible.

If you’d prefer to list the criteria in-formula, as opposed to referencing a range (E1:E3) containing those criteria, then let me know.

And remember that array formulas need confirming with CTRL+SHIFT+ENTER, not just ENTER.

Regards

48. Casey says:

This is very interesting stuff. Thank you for the guidance! If time permits, I would be interested to learn how you include the criteria in the formula as opposed to a range. Thanks again!

49. Ok, a bit more unwieldy, but in that case you would need:

`=SUM(IF(Sheet1!\$F1:\$F20=\$A6,IF(MMULT(0+ISNUMBER(SEARCH({"#TAG1","#TAG2","#TAG3"},Sheet1!\$J1:\$J20)),ROW(INDEX(A:A,1):INDEX(A:A,COUNTA({"#TAG1","#TAG2","#TAG3"})))^0)>0,1)))`

Amend the entries in the part:

`{"#TAG1","#TAG2","#TAG3"}`

as required.

Regards

50. Stefan Oana says:

Dear XOR,

I am trying to find a solution to a problem that I think you solved but I cannot find on your blog.

If you can please give me a hit.
I have to columns:
A with projects, B with amounts.

In the projects column, in every cell could be stored more than 1 project by this form:

#2011|#2015|#2009

In the B columns are stored the amounts (if there are 3 projects, will be 3 amounts, and so on)

145|165|198.

What I need help with is finding the answer to the question:
What is the amount per #2015 project.

Thank you

51. Hello,

I am as many people here are struggling with a formula.

I found lots of useful things on this website, but start getting lost and not managing to get what i need out of formula and i am sure the fix is very easy, but when you are a bit of a novice with excel its hard to see. So please help me to figure this out.

I have two columns which have numbers in them 1-5. I want to count number of lines/units which will have 3 and above in either or both of the columns and count it once. So if i have 3 in one or both columns count as 1 and count it. I have tried different ways using your web and last one i have is:

`=SUM(COUNTIFS(G2:G68,{">=3"},H2:H68,{">=3"}))`

but it only counts where both conditions met i think…as if i count manually the number is greater. i have tried adding countifs within sum, then it count all cells containing 3+.

Many thanks

Juliana

52. Firas says:

I would like to count if only 2 columns meets a criteria whereas other columns do not meet it (need to exclude the person who also meet the criteria in other columns)

53. nirvick says:

can anyone say me how to count VD001 if its written like VD001, VD003, VD001, VD001 in one row. its all in one line

54. @Stefan

So are you wanting to report a total for just one row? Or for multiple rows? You only give one row’s worth of data, so it’s a little difficult to understand your requirement here, I’m afraid.

Can you clarify?

Regards

55. @Juliana

See my article here.

You can continue to use COUNTIFS here, though it’s arguably a touch convoluted (the logic, however, is sound):

`=SUM(COUNTIFS(G2:G68,{">=",">=","<"}&3,H2:H68,{">=","<",">="}&3))`

More easily understandable (and extendible) is the switch to SUMPRODUCT:

`=SUMPRODUCT(0+((G2:G68>=3)+(H2:H68>=3)>0))`

Regards

56. @nirvick

Your question is not clear to me, I’m afraid. Can you clarify with an example plus expected result?

Regards

57. @Firas

Can you clarify your question with an example or two?

Regards

58. EJ says:

Excellent Explanation …

59. @EJ

Regards

60. ben says:

Hi,

Thanks for your articles, I find them really useful. However i’m struggling to convert the following countifs formula to something that works.

`=SUM(COUNTIFS(data!\$D:\$D,{10144;11440},data!\$P:\$P,{"C","U"},data!\$J:\$J,{"O","Z"}))`

I’m trying to return the number of rows that have 10144 or 11440 in column D, that have a C or U in column P and an O or Z in column J.

61. @ben

See the part in this article in which I give an explanation as to why this set-up with COUNTIF(S) fails for scenarios involving multiple “OR” criteria for more than two criteria_ranges.

In such cases we must switch to SUMPRODUCT, though of course this means that using entire column references is not at all a good idea (hence my choice of an upper row limit of 1000 in the below), viz:

`=SUMPRODUCT(0+ISNUMBER(MATCH(data!\$D1:\$D1000,{10144;11440},0)),0+ISNUMBER(MATCH(data!\$P1:\$P1000,{"C","U"},0)),0+ISNUMBER(MATCH(data!\$J1:\$J1000,{"O","Z"},0)))`

Regards

62. ben says:

thats great. thanks for your help

63. You’re welcome!

64. Ger says:

XOR LX – great tip… thank you.. honestly never knew about specifying an “OR” in the countifs.

My “OR” criteria was a range in itself, where I have used it simply to count the occurences of all values from one Column in another column. Works great, but strangely I needed to enter it is an array formula… but it still worked a treat. For example-

`=SUM(COUNTIFS(B4:B16,C4:C16))`

Thanks for the tip.

65. Ger says:

Also, equivalently…. from your tips above…

`=SUM(0+ISNUMBER(MATCH(C4:C16,B4:B16,0)))`

again array entered.

Everyday is a school day 🙂

Thanks.
Ger

66. Thanks, Ger.

Yes, I perhaps should’ve mentioned a case or two in which the criteria are contained within an actual worksheet range, rather than in-formula.

And you’re correct that, in such cases, SUM alone (i.e. without CSE) is not sufficiently strong to coerce the array, whereas it is when the criteria are hard-coded within the formula. FYI SUMPRODUCT (without CSE) will also work in your case.

Cheers