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

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” criteria.

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

67. Aaron says:

I have a series of cells with an ‘X’. I need to return a result in one cell based on which column the ‘X’ is in.

The following formula works perfect for one cell:

`=IF(SUM(COUNTIF(L2,{"*X*"})),"TEN YEAR MECHANICAL","")`

But I want to know if there is a way to have a different result returned based on which cell the ‘X’ is in. The series is L2:R2 and only one cell in that series has an ‘X’. If the ‘X’ is in M2 I want a different result than if it was in L2.

I am just not sure how to construct the formula to work with a series of cells and return different results. Any help is greatly appreciated.

68. UniMord says:

I enjoyed your article, and am trying to put it to good use, but no success yet.

Each row in my table contains a Claim # and a Service Code.

A single claim will have as many rows as there are service codes.

There are 2 sets of codes:

1) A0100, S0215
2) A0130, S0209

Each claim must contain 1 or more codes from either set, but not from both.

I need a column to identify claims with conflicting codes.

My current, pre-EXCELXOR, working formula is:

`=AND(SUM(COUNTIFS([CLMNUM], [@CLMNUM], [SVCCOD], {"A0100","S0215"})), SUM(COUNTIFS([CLMNUM], [@CLMNUM], [SVCCOD], {"A0130","S0209"})))`

My attempts at using a single COUNTIFS or SUMPRODUCT, neither of which work, are:

`{=SUM(IF([CLMNUM]=[@CLMNUM], COUNTIFS([SVCCOD],{"A0100 ";"S0215 "}, [SVCCOD],{"A0130 ","S0209 "})))}`
`=SUMPRODUCT(([CLMNUM]=[@CLMNUM])*(([SVCCOD]={"A0100","S0215"})*([SVCCOD]={"A0130";"S0209"})))`

Where am I going wrong?

Thank you,

UniMord

69. @Aaron

This is quite straightforward, but could you first just clarify a couple of things?

1) In which cells are the corresponding results? L1:R1 perhaps?
2) Do the cells contain "X" precisely? Or some word/phrase which contains an "X"?

Regards

70. @UniMord

You say that “Each claim must contain 1 or more codes from either set, but not from both“. However, your “working formula” would seem to me to return TRUE when a given claim contains codes from both sets.

Can you clarify where I’m mistaken, perhaps using a small example dataset?

Regards

71. UniMord says:

@XOR LX

Exactly, TRUE identifies the claims containing the invalid codes.

Here’s a small sample:

CLMNUM SVCCOD CLASH?
10/24/2017•148•50 A0130 FALSE
10/24/2017•148•50 S0209 FALSE
10/24/2017•148•51 A0100 FALSE
10/24/2017•148•51 S0215 FALSE
10/25/2017•153•87 A0100 TRUE
10/25/2017•153•87 S0209 TRUE
10/24/2017•153•88 A0100 FALSE
10/24/2017•153•88 S0215 FALSE
10/26/2017•150•71 A0100 TRUE
10/26/2017•150•71 A0130 TRUE
10/26/2017•150•71 S0215 TRUE

72. @UniMord

Ah, of course. Many thanks for clarifying.

By storing the codes in an appropriate 2-dimensional array, you can shorten the required formula a touch:

`=AND(MMULT(COUNTIFS([CLMNUM],[@CLMNUM],[SVCCOD],{"A0100","S0215";"A0130","S0209"}),{1;1})>0)`

Note that, if you’re not using an English-language version of Excel, the separators within the array constants in the above may need amending.

Regards

73. UniMord says:

@XOR LX

Thank you very much for your answer. I’m new to MMULT, and am always happy to smuggle one into my work, when the opportunity arises.

As clever as your approach is, and as eager as I am to employ it, I’m left wondering just how extensible it is.

The original example benefits from the happy coincidence that both sets of codes are symmetrical. But, what if they were 3 and 2? For example:

1) A0100, S0215, A0170
2) A0130, S0209

My original formula readily yields to the new circumstances with minimal adjustment:

`=AND(SUM(COUNTIFS([CLMNUM], [@CLMNUM], [SVCCOD], {"A0100","S0215","A0170"})), SUM(COUNTIFS([CLMNUM], [@CLMNUM], [SVCCOD], {"A0130","S0209"})))`

Furthermore, what if rather than 2 sets of codes, there were 3 or more sets that needed to be kept distinct? Once again, there is no limit on the first formula, although it keeps getting longer with every new set. So, if the sets were:

1) A0100, S0215, A0170
2) A0130, S0209
3) A0425, A0428

The following would work:

`=SUM(SUM(COUNTIFS([CLMNUM], [@CLMNUM], [SVCCOD], {"A0100","S0215","A0170"}))>0, SUM(COUNTIFS([CLMNUM], [@CLMNUM], [SVCCOD], {"A0130","S0209"}))>0, SUM(COUNTIFS([CLMNUM], [@CLMNUM], [SVCCOD], {"A0425","S0428"}))>0)>1`

Thank you.

74. @UniMord

You underestimate the extensibility of such set-ups with MMULT!

We can simply incorporate null strings so as to maintain an array constant of legitimate dimensions. For example, for your last:

`=SUM(N(MMULT(COUNTIFS([CLMNUM],[@CLMNUM],[SVCCOD],{"A0100","S0215","A0170";"A0130","S0209","";"A0425","S0428",""}),{1;1;1})>0))>1`

Of course, it’s usually preferable to reference a worksheet range than to store such array constants in-formula, for reasons of editability, clarity and conciseness. For example, using B1:D3 to hold these values (with D2 and E2 left empty), the above becomes:

`=SUM(N(MMULT(COUNTIFS([CLMNUM],[@CLMNUM],[SVCCOD],\$B\$1:\$D\$3),{1;1;1})>0))>1`

The second array being passed to MMULT ({1;1;1}) is here static, and dependent upon the maximum number of codes in any one set, but we can of course make it dynamic, e.g.:

`=SUM(N(MMULT(COUNTIFS([CLMNUM],[@CLMNUM],[SVCCOD],\$B\$1:\$D\$3),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(\$B\$1:\$D\$3)))^0)>0))>1`

Regards

75. UniMord says:

@XOR LX

Thank you for explicating; I’m now a believer.

76. @UniMord

You’re very welcome! Another MMULT convert! 🙂

77. Martin Meldrum says:

Hi, I like the explanations you have done above, a few have helped me with other formula.

I am trying to change this – :

`=SUM(COUNTIFS(F35:Z35,{">224"},F35:Z35,{"CG22"},F35:Z35,{"CG22"}))`

differently, but can not find anything on this ?

If you can help that would be great !

78. Lune says:

You are the actual best. My conditions are rather complicated, but now instead of adding two huge COUNTIF formulas together, I can just use a formula modeled on your fourth formula there

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

and get EXACTLY what I need. This way, the formula’s only half as monstrous. Thank you so much!!

79. Hi Lune,

Glad to hear it! You’re most welcome!

Cheers

80. Neel says:

Hi,

I’m trying to count unique values of column “A” base on my criteria but getting error:-

`=SUMPRODUCT(IF(FREQUENCY(IF(Export!H:H={"Flex w/o filters","Flex with filters"},IF(Export!K:K="18",IF(Export!J:J="H2",MATCH(Export!A:A,Export!A:A,0)))),ROW(Export!A:A)-MIN(ROW(Export!A:A))+1),1))`

81. @Neel

What error are you getting?

I’d also strongly recommend not using entire column references within such a formula, or else Excel will struggle to process it. Use e.g. H1:H1000, K1:K1000, etc., or some suitably sized ranges instead.

Regards

82. Jinky Zuniga says:

Hello, Im Trying to create a formula for this one, I need to count distinct values per week, only the “Open” status. The hard part is, you dont need to count the”Open” status if at a certain time that week it was already closed.

SR Reference # Wk # Status
19999 32 Closed
189898 32 Open
189898 32 Closed
189898 29 Closed
189898 31 Open
19999 33 Open
19999 33 Open

83. Hi

So can you just clarify the expected result for the sample you give?

Regards

84. Jinky Zuniga says:

hello expected result is

Summary:
Wk# Result
29 0
31 1
32 0
33 1

85. Thanks.

So, assuming the original table you give is in A1:C8 (with headers in row 1), and that you put your first Week # of choice (e.g. 29) in E2, this array formula** in F2:

`=SUM(IF(FREQUENCY(IF(B\$2:B\$8=E2,IF(C\$2:C\$8="Open",IF(COUNTIFS(B\$2:B\$8,E2,A\$2:A\$8,A\$2:A\$8,C\$2:C\$8,"Closed")=0,A\$2:A\$8))),A\$2:A\$8),1))`

Copy down to give similar results for other Week #s in E3, E4, etc.

Note that the above assumes that the SR Reference #s are all numeric, not text (i.e. they would return TRUE if tested with the ISNUMBER function).

Regards

**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).

86. Jinky Zuniga says:

Thank you! is it applicable if the rest of the table are still blanks? say A9- A100??

87. Jinky Zuniga says:

it worked well, thank you very much!

88. You’re welcome!

Cheers

89. Jinky Zuniga says:

hello again, what if the SR reference is a text and not a number? May i know the formula for that?

90. Then the formula is slightly longer:

`=SUM(IF(FREQUENCY(IF(B\$2:B\$8=E2,IF(C\$2:C\$8="Open",IF(COUNTIFS(B\$2:B\$8,E2,A\$2:A\$8,A\$2:A\$8,C\$2:C\$8,"Closed")=0,MATCH(A\$2:A\$8,A\$2:A\$8,0)))),ROW(A\$2:A\$8)-MIN(ROW(A\$2:A\$8))+1),1))`

Regards

91. Jinky Zuniga says:

hello thank you for the the big help!

92. You’re very welcome!

93. Don Ken says:

94. LH says:

I’m attempting to apply the solution you provided above to Casey and am not getting the expected result.

I have spreadsheet with columns for Company Name, Department Name, PC Name, and App Name (in that order).

I’m trying to count the number of “software pack” occurrences per Company. A “software pack” would be a defined grouping of applications (e.g. Visio, Project, and Adobe) and if a PC had any of these apps, they would get counted.

Right now I’m using the formula

`=SUM(IF('Data Set'!\$A\$1:\$A\$20="Company A",IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE('Data Set'!\$H\$1:\$H\$3),'Data Set'!\$D\$1:\$D\$20)),ROW('Data Set'!\$H\$1:\$H\$3)^0)>0,1)))`

In cells H1:H3 I have
*visio*
*project*

(in the actual data, there are entries for each specific version and I don’t want to include all possible variations)

The formula as it stands seems to be counting the instances of the software correctly, but not limiting it to one count per unique PC.

What am I missing from the formula? I’m thinking it’s another nested IF to account for matching PC Names, but can’t figure out how to account for that.

95. Hi,

I’m a little unsure as to why you haven’t included any reference to the PC Name column within your formula, given that you wish the count to be limited “to one count per unique PC”.

To clarify, could you perhaps post a very small, mock dataset (e.g. 10 rows’ worth), together with expected results?

Regards

96. LH says:

Agreed, I can’t figure out where a reference to PC Name should go based on the examples I’ve seen in this thread.

Here’s some sample data as CSV:

Company,Department,PC Name,PC IP,App Name,,,*Visio*
Company B,Accounting,PC4,1.1.1.4,Email,,,
Company B,HR,PC6,1.1.1.6,Project,,,
Company B,HR,PC6,1.1.1.6,Email,,,
Company C,HR,PC7,1.1.1.7,Visio 2019,,,
Company C,HR,PC7,1.1.1.7,Email,,,
Company C,Accounting,PC8,1.1.1.8,Project,,,
Company C,Accounting,PC10,1.1.1.10,Visio 2019,,,
Company C,Accounting,PC11,1.1.1.11,Email,,,
Company C,HR,PC12,1.1.1.12,Email,,,
Company C,IT,PC13,1.1.1.13,Email,,,

My expected results are:

| Company A | Company B | Company C
Office Pack | 3 | 3 | 4

97. Thanks a lot.

You’d need the following variation, then, I believe:

`=SUM(IF(FREQUENCY(IF('Data Set'!\$A\$1:\$A\$20="Company A",IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE('Data Set'!\$H\$1:\$H\$3),'Data Set'!\$D\$1:\$D\$20)),ROW('Data Set'!\$H\$1:\$H\$3)^0),MATCH('Data Set'!\$C\$1:\$C\$20,'Data Set'!\$C\$1:\$C\$20,0))),ROW('Data Set'!\$A\$1:\$A\$20)-MIN(ROW('Data Set'!\$A\$1:\$A\$20))+1),1))`

Note that this set-up assumes there are no empty cells within the range C1:C20.

Regards

98. LH says:

Nope, no blank cells in that range so it works great!! Thank you so much 🙂

99. I appreciate that this post is old but it caught my eye and I have created an Excel file from it.

As I usually do in cases like this, I translate everything to database functions, DCOUNT() in this case. I easily replicated everything since, as you undoubtedly know, database functions can happily cope with AND and OR situations.

However, I cannot replicate the formula

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

In my database set up, my answer is 5 while yours is 2

I even drew a decision tree to try to understand your solution but I still get five.

I am sure I am failing to program something properly but I cannot see what!

I appreciate you cannot see my work but if you could explain and defend your answer of 2, that would suffice, I think.

Your blog is a revelation, by the way: fascinating.

100. @LH You’re welcome! Cheers.

101. @excel2007master

Thanks for your kind words. I am glad that you have found some of the material I post here of interest.

As to a justification of my answer to the particular case you give, I’m not sure how much more I can add to the explanation I give in the post (beginning “Now let’s see what happens if we attempt to extend the criteria for column D to more than a single…”).

Perhaps you didn’t quite grasp the discussion re orthogonality? Is that the error you have made in your equivalent D-function calculations, i.e. not ensuring that the relevant arrays are of the same orthogonality as those I give?

Perhaps you could share your attempt at the equivalent D-function set-up?

Regards

102. I apologise. Having read your post again, I see that you have discussed my point more at length than I first thought. Still, I cannot see how to get 2 rather than 5

I have your data set in A1: D14

In I1:K13, I set up a table with Sex Pet Hobby as column headers, then

Male Mite Genealogy
Male Mite Roleplay
Male Sea lion Genealogy
Male Sea lion Roleplay
Female Mite Genealogy
Female Mite Roleplay
Female Sea lion Genealogy
Female Sea lion Roleplay

Finally,

`=DCOUNT(A1:D14,,I1:K13)`

I would truly appreciate your correction and thank you for your rapid response of yesterday!

Duncan

103. I must urge you to read again the section which I mentioned in my previous comment. I appreciate that it is rather technical in nature, though explains precisely why a count of 2 is obtained for that construction.

The table you are using as your DCOUNT criteria contains ‘pairs’ of entries corresponding to Male/Genealogy, Male/Roleplay, Female/Basketball and Female/Roleplay. As I explain in the post, however, none of these pairs is considered in the COUNTIFS construction to which we are referring; in fact, the only two ‘pairs’ which it does evaluate relate to Male/Basketball and Female/Genealogy. If you were to delete all rows within your criteria table not relating to either of these pairs then you too would obtain a result of 2.

If we are attempting to return a count for all possible permutations of Male/Female/Mite/Sea lion/Basketball/Genealogy/Roleplay, i.e. the 12 you correctly list in your criteria table, then DCOUNT returns the correct answer, whereas the COUNTIFS construction fails. The point of that section within this post was to attempt an explanation not only as to why the latter fails, but also as to why we obtain the (seemingly odd) result of 2. The crucial observation for the reader to hopefully take on board was that relating to (as I mentioned previously) orthogonality with respect to the array constants.

This issue of ‘dimensionality’ is a limitation of COUNTIFS which is not shared by other functions, such as SUMPRODUCT and, thanks to your bringing it to my attention, the D-functions.

Regards

104. Very good and I must work through you examples a little more seriously to ensure I fully grasp your point.

I am a bit of a database function evangelist, I’m afraid, as I see the kind of people you describe, hailed as gurus and worse, telling us to use VLOOKUP, INDEX-MATCH and all the rest of them. I even saw a demo using SUMPRODUCT and SUMIFS in which, as they developed their models, the formulas grew and grew and grew. So, I built the appropriate Database model in which the formula never changes in size, although the input table might.

In those settings, I always think database functions win but I will work on yours again.

I appreciate your responses: not all bloggers can be bothered to reply and some of them are quite rude if you question their approach or suggest an alternative! I only ever write for greater understanding.

105. There’s no more commendable a goal than that which strives for greater understanding.

Glad to have helped and thanks also for bringing back to my attention the Database functions, which I feel are due a revisit on my part.

Cheers

106. Jinky Zuniga says:

hello, im trying to create a formula for where in i have 1 page for the output, 2nd page is the source.

2nd page content is:
Mat Code Item Name Total Quantity Lamda Coco Lens
H Family H Family
H78999 AH Material A 1 0 1 0
H79000 AH Material B 1 1 0 0
H79001 AH Material C 0 0 0 0
H79002 AH Material D 1 0 0 1
Y Family Y Family
Y42927 YH Material X 1 1 0 0
Z Family Z Family
Z39337 ZH Material X 2 0 2 0
Z39338 ZH Material X 3 1 1 1

1st page output:
Name List Lamda

Mat Code Item Name Quantity
H Family H Family
H78999 AH Material A 1
Y Family Y Family
Y42927 YH Material X 1
Z Family Z Family
Z39338 ZH Material X 1

By entering each name list on the 1st page, matcode will appear on the column without the zero value. Hope you can help me on this. Thanks!

107. @Jinky Zuniga

Unfortunately from your pasted data it’s not clear what lies in which columns. Can you clarify?

Regards

108. Jinky Zuniga says:

Hello,
Sorry for the inconvenience. here is a clearer data
2nd page content is:

Column1 Column2 Column3 Column4 Column5 Column6
MatCode ItemName TotalQty Lambda Coco Lens
H78999 MaterialA 1 0 1 0
H79000 MaterialB 1 1 0 0
H79001 MaterialC 0 0 0 0
H79002 MaterialD 1 0 0 1
Y42927 MaterialY 1 1 0 0
Z39337 MaterialZ 2 0 2 0
Z39338 MaterialX 3 1 1 1

1st page output:
if I want to know those items in Lambda column except zero this is the output:

Lambda
MatCode ItemName Qty
H79000 MaterialB 1
Y42927 MaterialY 1
Z39338 MaterialX 1

Thanks

109. Thanks. Then you’d need something like:

`=IFERROR(INDEX(Table1[MatCode],AGGREGATE(15,6,(ROW(Table1[MatCode])-MIN(ROW(Table1[MatCode]))+1)/(Table1[Lambda]0),ROWS(\$1:1))),"")`

and copied down.

You can use the same formula to give the results for the ItemName and Qty columns, changing the column being referenced (MatCode) accordingly.

Regards

This site uses Akismet to reduce spam. Learn how your comment data is processed.