Excel COUNTIF and COUNTIFS with OR logic

The tutorial explains how to use Excel's COUNTIF and COUNTIFS functions to count cells with multiple OR conditions, e.g. if a cell contains X, Y or Z.

As everyone knows, Excel COUNTIF function is designed to count cells based on just one criterion while COUNTIFS evaluates multiple criteria with AND logic. But what if your task requires OR logic - when several conditions are provided, any one can match to be included in the count?

There are a few possible solutions to this task, and this tutorial will cover them all in full detail. The examples imply that you have a sound knowledge of the syntax and general uses of both functions. If not, you may want to begin with revising the basics:

Excel COUNTIF function - counts cells with one criteria.

Excel COUNTIFS function - counts cells with multiple AND criteria.

Now that everyone is on the same page, let's dive in:

Count cells with OR conditions in Excel

This section covers the simplest scenario - counting cells that meet any (at least one) of the specified conditions.

Formula 1. COUNTIF + COUNTIF

The easiest way to count cells that have one value or another (Countif a or b) is to write a regular COUNTIF formula to count each item individually, and then add the results:

COUNTIF(range, criterion1) + COUNTIF(range, criterion2)

As an example, let's find out how many cells in column A contain either "apples" or "bananas":

=COUNTIF(A:A, "apples") + COUNTIF(A:A, "bananas")

In real-life worksheets, it is a good practice to operate on ranges rather than entire columns for the formula to work faster. To spare the trouble of updating your formula every time the conditions change, type the items of interest in predefined cells, say F1 and G1, and reference those cells. For example:

=COUNTIF(A2:A10, F1) + COUNTIF(A2:A10, G1)
Count cells that have one value or another.

This technique works fine for a couple of criteria, but adding three or more COUNTIF functions together would make the formula too cumbersome. In this case, you'd better stick with one of the following alternatives.

Formula 2. COUNTIF with array constant

Here's a more compact version of the SUMIF with OR conditions formula in Excel:

SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))

The formula is constructed in this way:

First, you package all the conditions in an array constant - individual items separated by commas and the array enclosed in curly braces like {"apples", "bananas', "lemons"}.

Then, you include the array constant in the criteria argument of a normal COUNTIF formula: COUNTIF(A2:A10, {"apples","bananas","lemons"})

Finally, warp the COUNTIF formula in the SUM function. It is necessary because COUNTIF will return 3 individual counts for "apples", "bananas" and "lemons", and you need to add those counts together.

Our complete formula goes as follows:

=SUM(COUNTIF(A2:A10,{"apples","bananas","lemons"}))
COUNTIF with an array constant to count cells with OR logic

If you'd rather supply your criteria as range references, you'll need to enter the formula with Ctrl + Shift + Enter to make it an array formula. For example:

=SUM(COUNTIF(A2:A10,F1:H1))

Please notice the curly braces in the screenshot below - it is the most evident indication of an array formula in Excel:
Array formula to sum cells based on criteria as range references.

Formula 3. SUMPRODUCT

Another way to count cells with OR logic in Excel is to use the SUMPRODUCT function in this way:

SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))

To better visualize the logic, this could also be written as:

SUMPRODUCT((range=criterion1) + (range=criterion2) + …)

The formula tests each cell in the range against each criterion and returns TRUE if the criterion is met, FALSE otherwise. As an intermediate result, you get a few arrays of TRUE and FALSE values (the number of arrays equals the number of your criteria). Then, the array elements in the same position are added together, i.e. the first elements in all the arrays, the second elements, and so on. The addition operation converts the logical values to numbers, so you end up with one array of 1's (one of the criteria matches) and 0's (none of the criteria matches). Because all the criteria are tested against the same cells, there is no way any other number could appear in the resulting array - only one initial array can have TRUE in a specific position, others will have FALSE. Finally, SUMPRODUCT adds up the elements of the resulting array, and you get the desired count.

The first formula works in a similar manner, with the difference that it returns one 2-dimentional array of TRUE and FALSE values, which you multiply by 1 to convert the logical values to 1 and 0, respectively.

Applied to our sample data set, the formulas take the following shape:

=SUMPRODUCT(1*(A2:A10={"apples","bananas","lemons"}))

Or

=SUMPRODUCT((A2:A10="apples") + (A2:A10="bananas") + (A2:A10="lemons"))

Replace the hardcoded array constant with a range reference, and you will get even a more elegant solution:

=SUMPRODUCT(1*( A2:A10=F1:H1))
SUMPRODUCT formula to count cells with OR logic

Note. The SUMPRODUCT function is slower than COUNTIF, which is why this formula is best to be used on relatively small data sets.

Count cells with OR as well as AND logic

When working with large data sets that have multi-level and cross-level relations between elements, chances are that you will need to count cells with OR and AND conditions at a time.

As an example, let's get a count of "apples", "bananas" and "lemons" that are "delivered". How do we do that? For starters, let's translate our conditions into Excel's language:

  • Column A: "apples" or "bananas" or "lemons"
  • Column C: "delivered"

Looking from another angle, we need to count rows with "apples and delivered" OR "bananas and delivered" OR "lemons and delivered". Put this way, the task boils down to counting cells with 3 OR conditions - exactly what we did in the previous section! The only difference is that you'll utilize COUNTIFS instead of COUNTIF to evaluate the AND criterion within each OR condition.

Formula 1. COUNTIFS + COUNTIFS

It is the longest formula, which is the easiest to write :)

=COUNTIFS(A2:A10, "apples", C2:C10, "delivered") + COUNTIFS(A2:A10, "bananas", C2:C10, "delivered")) + COUNTIFS(A2:A10, "lemons", C2:C10, "delivered"))

The screenshot below shows the same formula with cells references:

=COUNTIFS(A2:A10, K1, C2:C10, K2) + COUNTIFS(A2:A10, L1, C2:C10, K2) + COUNTIFS(A2:A10, M1,C2:C10, K2)
Add two or more COUNTIFS to count cells with OR as well as AND logic.

Formula 2. COUNTIFS with array constant

A more compact COUNTIFS formula with AND/OR logic can be created by packaging OR criteria in an array constant:

=SUM(COUNTIFS(A2:A10, {"apples","bananas","lemons"}, C2:C10, "delivered"))

When using a range reference for the criteria, you need an array formula, completed by pressing Ctrl + Shift + Enter:

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))
COUNTIFS with an array constant to count cells with AND/OR logic

Tip. If needed, you are free to use wildcards in the criteria of any formulas discussed above. For example, to count all sorts of bananas such as "green bananas" or "goldfinger bananas" you can use this formula:

=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered"))

In a similar manner, you can build a formula to count cells based on other criteria types. For example, to get a count of "apples" or "bananas" or "lemons" that are "delivered" and the amount is greater than 200, add one more criteria range/criteria pair to COUNTIFS:

=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered", B2:B10, ">200"))

Or, use this array formula (entered via Ctrl + Shift + Enter):

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2, B2:B10, ">"&F3))
Count cells based on three AND/OR criteria of different types.

Count cells with multiple OR conditions

In the previous example, you have learned how to test one set of OR conditions. But what if you have two or more sets and you are looking to get a total of all possible OR relations?

Depending on how many conditions you need to handle, you can use either COUNTIFS with an array constant or SUMPRODUCT with ISNUMBER MATCH. The former is relatively easy to build, but it is limited to only 2 sets of OR conditions. The latter can evaluate any number of conditions (a reasonable number, of course, given Excel's limit to 255 arguments and 8192 characters to the total formula length), but it may take some effort to grasp the formula's logic.

Count cells with 2 sets of OR conditions

When dealing with only two sets of OR criteria, just add one more array constant to the COUNTIFS formula discussed above.

For the formula to work, one minute but critical change is needed: use a horizontal array (elements separated by commas) for one criteria set and vertical array (elements separated by semicolons) for the other. This tells Excel to "pair" or "cross-calculate" the elements in the two arrays, and return a two-dimensional array of the results.

As an example, let's count "apples", "bananas" or "lemons" that are either "delivered" or "in transit":

=SUM(COUNTIFS(A2:A10, {"apples", "bananas", "lemons"}, B2:B10, {"delivered"; "in transit"}))

Please note the semicolon in the second array constant:
Count cells with 2 sets of OR conditions.

Because Excel is a 2-dimentional program, it is not possible to construct a 3-dimentional or 4-dimentuional array, and therefore this formula only works for two sets of OR criteria. To count with more criteria, you will have to switch to a more complex SUMPRODUCT formula explained in the next example.

Count cells with multiple sets of OR conditions

To count cells with more than two sets of OR criteria, use the SUMPRODUCT function together with ISNUMBER MATCH.

For example, let's get a count of "apples", "bananas" or "lemons" that are either "delivered" or "in transit" and are packaged in either "bag" or "tray":

=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))*
ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))*
ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))

In the heart of the formula, the MATCH function checks the criteria by comparing each cell in the specified range with the corresponding array constant. If the match is found, it returns a relative position of the value if the array, N/A otherwise. ISNUMBER converts these values to TRUE and FALSE, which equate to 1 and 0, respectively. SUMPRODUCT takes it from there, and multiplies the arrays' elements. Because multiplying by zero gives zero, only the cells that have 1 in all the arrays survive and get summed.

Th screenshot below shows the result:
Count cells with multiple sets of OR conditions.

This is how you use the COUNTIF and COUNTIFS functions in Excel to count cells with multiple AND as well as OR conditions. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

Practice workbook

Excel COUNTIF with OR conditions - examples (.xlsx file)

Latest comments

  1. Hi there,

    This is a great tutorial. I have something that I need a solution for.

    I have 3 columns, Process (column A), Work Started (Column B), and Work Completed (Column C).

    I need to know how many "Environmental Screening" Processes in Column A were worked on and/or completed in "2024/1st". I need it to count an environmental screening as 1 if it has "2024/1st" in column B OR column C, but also count an environmental screening as 1 if it has "2024/1st" in column B AND column C, I don't want to count a single environmental screening twice. I hope that makes sense.

    I was thinking that I needed to string together an AND function and OR functions to accomplish this; an OR function for the (Work Started = "2024/1st") + (Work Completed = "2024/1st") nested into an AND function along with (Process = "Environmental Screening"). However, I can't seem to create a formula without getting an error message.

    Any insight is appreciated

    1. Hello Joe!
      In your case, to calculate values based on conditions, use SUMPRODUCT function.

      =SUMPRODUCT((A2:A100="Environmental Screening") * ((B2:B100="2024/1st")+(C2:C100="2024/1st")>=1))

      or

      =SUMPRODUCT((A2:A100="Environmental Screening") * ((B2:B100="2024/1st") + (C2:C100="2024/1st") > 0))

      You can find more examples and detailed instructions here: Excel SUMPRODUCT function with multiple criteria.

  2. Hi – I generate a list of stock symbols in an array (D20#) from the following formula:
    =FILTER(Journal[Symbol],(Journal[$ P&L]>0)*(Journal[Entry
    Date]>=$C$3)*(Journal[Entry
    Date]0)*(Journal[Entry
    Date]>=$C$3)*(Journal[Entry
    Date]<=$E$3)))

    Subsequently I use a formula =COUNTIF(D20#,C20#) to count the number of times a particular stock is present. However, I am unable to develop a single cell formula to make the calculation in Step 3 (Countif). Can you please guide me? Thanks.

    1. Hi! In Excel formula =COUNTIF(D20#,C20#) uses spilled range references (#), meaning that D20# and C20# represent dynamic arrays.
      However, COUNTIF does not support array references directly. Instead, you can use SUM formula with a logical condition:

      =SUM(--(D20#=C20#))

      This formula compares values in both arrays and counts the matches.
      If you need to count occurrences based on a specific condition, SUMPRODUCT function can be useful:

      =SUMPRODUCT(--(D20#="condition"))

  3. Hi Alexander, this is the BEST tutorial! But I must be doing something wrong, because I cannot get this to work for me. Of course, I am trying to do something more complicated!

    I want the formula to read a text in Column I, and return "Week 2", "Week 3" etc in another column for easy filtering. I was thinking/hoping that this would work: =COUNTIFS(I2,"Week 2"&"*"),"Week 2",(I2,"Week 3"&"*"),"Week 3",(I2,"Week 4"&"*"),"Week 4",(I2,"Week 5"&"*"),"Week 5",(I2,"Week 6"&"*"),"Week 6",""))))

    But it doesn't, and I cannot get Excel to even accept the formula (I get a "There's a problem with this formula" message which doesn't help me in the slightest). Once I get this working, I will drag this formula down the 3000 lines of data.

    Is there something obvious that I have done incorrectly?

  4. Hello Alexander,

    Thank you so much for your GREAT tutorial.
    This really really help my work life more easier, especially for "COUNTIF with array constant"

    The formulation is workable for several wording criteria. But it's not workable when I combined different type of criteria, which is date & wording.
    Such as, this criteria ">"&TODAY(). I would like to count items which included status "date after today" or "TBC".

    Could you please help to sharing solution for make this workable "=SUM(COUNTIFS($I:$I,{">"&TODAY(),"TBC"}))?

    Thank you in advance for your great suggestion :)

  5. A couple questions:

    1. When applying multiple 'OR' criteria to a range, can one supply both text and cell reference criteria in a compact format, i.e., something like:

    =SUM(COUNTIF(A2:A10,{$F$1,"bananas","lemons"}))
    =SUM(COUNTIF(A2:A10,{&$F$1,"bananas","lemons"}))

    My Excel 2010 doesn't like these formulas. Is there another way using countif to apply multiply OR criteria to a range without adding multiple countifs together using +?

    2. Does Excel allow one to use functions within criteria in countif? Specifically, I would like to use not(), isblank(), left(), etc. I am thinking something like:

    =SUM(COUNTIF(A2:A10,not($F$1)))
    =SUM(COUNTIF(A2:A10,left($F$1,6)))

    Thanks.

    1. Hello! To apply multiple 'OR' criteria to a range, you can use the SUMPRODUCT function. For example:

      =SUMPRODUCT(--(A1:A10={"bananas","lemons"}))

      Look for the example formulas here: Excel SUMPRODUCT function with multiple criteria.
      You can use separate operators and functions within the COUNTIF criterion. Learn more here: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique.
      You can perform similar calculations using the SUMPRODUCT function:

      =SUMPRODUCT(--(A1:A10<>"bananas"))

      You can also count partial matches with the criterion:

      =SUMPRODUCT(--ISNUMBER(SEARCH(LEFT(F1,6),A1:A10)))

      For more information, please read: How to find substring in Excel

  6. I am trying to check three columns (say, A, B and C) for a particular region and, if the region it appears in any of the columns, then it should check a fourth column (say D) for another criteria (outcome) and count how many rows meet the outcome criteria. That would be simple if a particular region only appeared in column A or B or C. The problem I have is that the region may appear in one row in both columns A and B but I don't want it to count that as two (i.e. it must only count the region once if it appears in any of the three region columns AND meets the outcome). But if it appears in only column B and meets the outcome criteria, then it also only counts once. I've tried many ways to do this and just cannot seem to get it to work but I know it must be do-able.

  7. I need formula for differentiating duplicate values in column with integer in the end. For example, if there are 3 duplicate values in column i.e. "Apple" and I want to differentiate word "Apple" with "Apple-1", "Apple-2", "Apple-3" in next column.

  8. I have a spread sheet with two columns that I want to COUNTIF with if an OR statement is true.

    The two columns (Call them A and B) both have optional values of "Y", "N", or blank. I want to set up a count of the number of rows where either column's value is "Y". Also I would like to display that as a percentage of 'Not Blank' (i.e. anything in column A or B)

    For Example if A1,2 and 3 are "Y", B3 & 4 are "Y", B5 is "N" and A&B 6 are blank then the count returns "4" and the percentage "80%"

  9. Hello, I am creating a tracker to account for number of tickets purchased based by pay grade in this example E8 is the paygrade. I am also trying to account for number of tickets purchased by that paygrade as their guest and I am completely lost. I don't know what to put in the ??????? spot or if this is even the correct formula.

    Example, John's paygrade is E8 who purchased 1 ticket for himself and 2 guest tickets, Michael's paygrade if E8 who purchased 1 ticket for himself and 3 guest tickets. I want to produce one cell that will count total number of E8's who purchased a ticket and the cell next to it I want to count total number of guest tickets purchased by all E8's. So one cell should have (2) and the cell adjacent should have (5) for this example.

  10. if columns B1,2,4 are Y and B,3 is N, what formula will give me a % of meetings attended for a 12 month period

  11. I have a spreadsheet where I am using the following countifs formulas: =COUNTIFS(LOG!$H$2:$H$1249,"*Initial*",LOG!$K$2:$K$1249,"Anderson*")
    I also need to add in one more item to consider: how may of those were received in a certain month. How would I do that?

  12. If a student gets mark less than 16 or his average be less than 50 he must be in group D. Please write a formula for it

  13. Hello!
    My husband is trying to create a spreadsheet for work and cannot seem to find a formula that works.
    He is trying to use the COUNTIFS function to count all cells in a range that are within separate number ranges.

    He needs the cells to be counted once if they are below 517, or between 525 and 896, or above 905. Every formula we have tried has not worked. It either does not count the cells, or counts them more than once.

    This is an example of one of the formulas we have tried:

    =COUNTIFS(C3:I4,">=0","525","905")

    I know this is not right, but I have scoured the internet but have not found a solution.

    Thank you!

  14. I want to count the number of cells that have a number in them, not if it is blank.

  15. Hi I am trying to do the following COUNT formula:

    If cells J20:J44 are greater than 0 but less than 50

    Thank you in advance

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)