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!

Pingback: Sumifs with or?

Is there any solution to applying this

@lakshyaHi and welcome to the site!

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

Regards

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

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.

A3 points to a data validation consisting of:

type3

type4

{“type1″;”type2”}

Do you have any suggestions?

@IggyVery 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

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

fails?

Hi Chris.

In such cases you need to switch to SUMPRODUCT:

Regards

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!

@Daniel SThanks for your kind words. 🙂

Re your query, it sounds like this other article of mine may be just what you require:

https://excelxor.com/2015/03/30/counting-rows-where-at-least-one-condition-is-met/

Let me know if you have any problems adapting the solution(s) given there to meet your needs.

Regards

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

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:

can be replaced with the shorter:

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

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.:

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

Hi XOR LX,

Thank you so much.

Everything is clear now.

Thanks for the step by step examples.

Highly appreciated.

Take Care.

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.

Thanks in advance

Andrew

Hi Andrew.

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

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

Regards

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?

Thanks in advance.

@AKBHi 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

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 –

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.

@puntingpalHi and welcome to the site!

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

Regards

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 > ???

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??

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

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:

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

Thank you for any advice.

@I Hate ExcelFirst of all, re your username, sorry to hear it! 🙂

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.:This can also be done via a single construction, viz:

though the latter is arguably less intuitive than the former.

Hope that helps!

Regards

This was amazingly thorough and helpful. Thank you!

@AldaGlad to hear it! And you’re very welcome!

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?

@JishaHi 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:

and copy down to E10.

Your required counting formula is then simply:

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

Hope that helps!

Regards

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

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

minimumfrom a given range. In fact, we can actually passmore than onerange to SUBTOTAL and it will return the minimum fromeachof 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:

would return a reference to the range:

A2:D2

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

Similarly:

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

rowsandcolsparameters of OFFSET; we can also pass multiple values (arrays) all at once.This is what I did here, since this part:

which I am passing as OFFSET’s

rowsparameter, generates not just a single value, but many. Specifically, the above resolves to the array of values:And so the construction:

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:

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

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

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

Hope that helps!

Thank you very much for the clear explanation. 🙂

Wondering if it needs to be this complicated.. 😀

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 =

here is my formula

@LeoHi 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

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

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

In that case you need to use an

array formula**(see instructions at the foot of this comment), i.e.: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).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.

your formula works perfectly!!!

Thanks again!!

You’re more than welcome!

Cheers!

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.

@JobMany 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!

Great Article.

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

So the above becomes

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

@San New to ExcelThanks! Glad you think so!

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.:

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.

notorthogonal to each other), that you are interested in obtaining a count for just thetwocombinations Male/Sea Lion and Female/Mite, and not allfour, i.e. Male/Sea Lion, Male/Mite, Female/Mite and Female/Sea Lion.Regards

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

for lines shipped

Lines backorder

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:

PO shipped:

Can you help me? Thank you in adavance

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

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

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

Regards

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

Wise words indeed! And you’re welcome!

Cheers

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”

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?

@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: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

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!

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

Amend the entries in the part:

as required.

Regards

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.

The answer is 165.

Could you please help me?

Thank you

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:

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

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)

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

@StefanSo 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

@JulianaSee my article here.

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

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

Regards

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

Regards

@FirasCan you clarify your question with an example or two?

Regards

Excellent Explanation …

@EJThanks! Glad you think so.

Regards

Hi,

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

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.

Thanks for your help.

@benSee 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 twocriteria_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:

Regards

thats great. thanks for your help

You’re welcome!

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-

Thanks for the tip.

Also, equivalently…. from your tips above…

again array entered.

Everyday is a school day 🙂

Thanks.

Ger

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

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:

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.

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:

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

Where am I going wrong?

Thank you,

UniMord

@AaronThis 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

@UniMordYou 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

@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

@UniMordAh, of course. Many thanks for clarifying.

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

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

@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:

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:

Thank you.

@UniMordYou 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:

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:

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.:

Regards

@XOR LX

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

@UniMordYou’re very welcome! Another MMULT convert! 🙂

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

I am trying to change this – :

differently, but can not find anything on this ?

If you can help that would be great !

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

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

Hi Lune,

Glad to hear it! You’re most welcome!

Cheers

Hi,

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

Could you please help me with this?

@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

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

Thanks in advance!

Hi

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

Regards

hello expected result is

Summary:

Wk# Result

29 0

31 1

32 0

33 1

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: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).Thank you! is it applicable if the rest of the table are still blanks? say A9- A100??

Yes

it worked well, thank you very much!

You’re welcome!

Cheers

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

Then the formula is slightly longer:

Regards

hello thank you for the the big help!

You’re very welcome!

Thank you for this article.Provided the missing link to complete my data analysis

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

In cells H1:H3 I have

*visio*

*project*

*adobe*

(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.

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

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 A,Accounting,PC1,1.1.1.1,Adobe,,,*Project*

Company A,Accounting,PC1,1.1.1.1,Visio 2019,,,*Adobe*

Company A,Purchasing,PC2,1.1.1.2,Email,,,

Company A,Purchasing,PC2,1.1.1.2,Adobe,,,

Company A,Purchasing,PC2,1.1.1.2,Project,,,

Company A,Purchasing,PC3,1.1.1.3,Visio 2010,,,

Company B,Accounting,PC4,1.1.1.4,Adobe,,,

Company B,Accounting,PC4,1.1.1.4,Email,,,

Company B,Purchasing,PC5,1.1.1.5,Visio 2010,,,

Company B,HR,PC6,1.1.1.6,Project,,,

Company B,HR,PC6,1.1.1.6,Adobe,,,

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,PC9,1.1.1.9,Adobe,,,

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,,,

Company C,Purchasing,PC14,1.1.1.14,Email,,,

My expected results are:

| Company A | Company B | Company C

Office Pack | 3 | 3 | 4

Thank you for your help!

Thanks a lot.

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

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

Regards

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

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

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.

@LHYou’re welcome! Cheers.@excel2007masterThanks 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

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 Basketball

Male Mite Genealogy

Male Mite Roleplay

Male Sea lion Basketball

Male Sea lion Genealogy

Male Sea lion Roleplay

Female Mite Basketball

Female Mite Genealogy

Female Mite Roleplay

Female Sea lion Basketball

Female Sea lion Genealogy

Female Sea lion Roleplay

Finally,

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

Duncan

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

criteriacontains ‘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 yourcriteriatable 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

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.

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

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!

@Jinky Zuniga

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

Regards

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

Thanks. Then you’d need something like:

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