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)

Table of contents

170 comments

  1. Hello!

    I have been trying to count the number of LGBTQ+ from an organization. However, the code

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

    does not seem to work. Here is a sample of my work. I hope you can visualize it.

    =SUM(COUNTIFS(E169:E209,{"P","Bi"}))

    Note: COUNTIFS or COUNTIF, still does not work. It does not count the "Bi"

  2. I am trying to count Yes and No in 1 cell but from multiple columns and then create a score in another column. So if I got 3 "yes" and 1 "no" the score would be 3. This what I got:
    =SUM(COUNTIF(U3,N3,X3,{"Yes",1})(U3,N3,X3,{"No",0}))
    But it did occur to me that I don't need to count the "No" so could I shorten it thus?
    =SUM(COUNTIF(U3,N3,X3,{"Yes",1})

    I'm getting errors saying I'm entering too many arguments and it doesn't work. Help!

  3. Hello!

    Trying to find a solution for nested conditions with no success so far. Please see below:

    Spreadsheet is looking to see if a range of cells (D:32:D35) is answered "Yes", If 1-2 cells in this range are answered "Yes", then cell L:31 will be assigned a value of .5. However if greater than 2 cells in the range answer "Yes". then L:31 will need to be 1.

    Any tips? Thank you!

  4. Since, after multiple attempts, I can't seem to get my formula to show correctly, can I email it? It shows fine, until I hit Send and then half of it is missing.

    • We apologize for this nasty behavior. Our blog engine often mangles formulas containing "less than" and "greater than" operators, and we can do nothing about it. You can email your formula to support@ablebits.com Att: Alexander Trifuntov

      Again, our apologies for the inconvenience.

    • Thank you for your emailed response, which worked.

  5. SUMPRODUCT(--(AND('Source Sheet'!$D:D>=A3,'Source Sheet'!$D:D0,1,0))

    • Aaarrrrgghhhh!!!!!

  6. I don't understand why the formulae are being truncated.

  7. Breaking it up, will see if that works

    =SUMPRODUCT(--(AND('Source Sheet'!$D:D>=A3,'Source Sheet'!$D:D0,1,0))

  8. I want to count, only once, those values that match ONE of a range of three criteria. Column A contains dates and multiple rows can have the same date. Columns B, C or D contain only "Y" or "N". I want to count, for each date, only those rows where a "Y" is in any one of B, C or D. If there is an N in all three, it is not counted. For example:

    01/01/2021 Y N N
    01/01/2021 N N Y
    01/01/2021 N N N
    01/01/2021 Y Y Y
    02/01/2021 Y Y Y

    The count against 01/01/2021 should return 3 above, because there is at least one Y in cols B, C or D on three of the four rows that match that date (where there is more than one, it should only be counted once).

    No variation of SUMPRODUCT, COUNTIFS etc I have come up with gives me the right number. Can you help please?

    • Hello!
      Please check the formula below, it should work for you:

      =SUMPRODUCT(--($A$1:$A$10=A1),IF(($B$1:$B$10="Y")+($C$1:$C$10="Y")+($D$1:$D$10="Y")>0,1,0))

      I hope this will help

      • It does for individual dates, thank you. I also have a monthly summary sheet. In this one, months (eg Jan-21, Feb 21 etc are in column A of the summary sheet, to be checked against individual dates on column D of the source worksheet and the data to be checked for a single Y are in columns E to G of the source worksheet. I have attempted to adapt the formula you gave but this returns a VALUE error. I am guessing it is a simple missed comma or bracket, or is it the AND throwing it out?

        =SUMPRODUCT(--(AND('Source Sheet'!$D:D>=A3,'Source Sheet'!$D:D0,1,0))

        This is attempting to count, for all dates between 1st and last date of the month in column A of the target sheet, where there is a Y on each row in any one of columns E to G in the source sheet,

        For example, if A3 on the target sheet were Mar-21, it would retrieve all values where the date is greater than or equal to 1st March 2021 and less than or equal to 31st March 2021 and, for the rows between those dates in the source sheet, count how many of those rows have at least one Y between cells E and G and output that number to the cell containing the above formula in the target sheet. Note, I have ruled out using just month numbers as the source sheet contains more than one year of dates.

        • Some of the formula was missing in my last comment. Trying again

          =SUMPRODUCT(--(AND('Source Sheet'!$D:D>=A3,'Source Sheet'!$D:D0,1,0))

  9. I am creating an spreadsheet for our Oncology dept which has the following sheets say 3 March, 4 March, 5 March etc and then a summary page. Each days sheet contains the Patient ID as well as what type, namely chemo, New File, Others etc. In the summary page I need to show a summary count with each of the category (Chemo,New Fileetc.) under a day column like this

    05 March 2021 06 March 2021 07 March 2021

    Morning Session - New File 1 #VALUE!
    Morning Session - Chemo 2
    Morning Session - Old 1
    Morning Session - Others 0

    ***** 4
    The COUNTIFS formula works when I dont have to evaluate the date to the current date which I have given as a heading and then in a cell in the days (6th March sheet). That throws an error. This is the formula I have used
    =COUNTIFS('6th March'!E5:E1335,"Chemo", ',Summary!C2,'6th March'!C2)
    Where the second criteria is the date I am trying to collate.

    Any help will be much appreciated.

    Thank you in advance.

    • Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.
      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
      We'll look into your task and try to help.

  10. Hi,

    I am currently working on a formula to track 3 different types of orders and their status. In the workbook I have the order type as well as the due date and completion date in separate columns. I also created 2 columns to calculate IF statements to determine if 1) the order is "overdue" (completion date>due date) and 2) if the completion date is "blank" to mark it as outstanding. Currently I have the formula set to count any of the specific order type that is marked as overdue or as outstanding (Formula:=COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!I:I, "OVERDUE")+COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!J:J, "OUTSTANDING"); however, I want to narrow it down further so that only pulls the overdue+outstanding count from a specific due date instead of from everything. What function should I use to go about this? Thank you!

    • Hello!
      If I understand your problem correctly, one more condition needs to be added to each of the COUNTIFS formulas. Like that:

      =COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!I:I, "OVERDUE",'Due Dates'!B:B,">"&T19 )+COUNTIFS('Due Dates'!$A:$A, T18,'Due Dates'!J:J, "OUTSTANDING",'Due Dates'!B:B,">"&T19)

      where T19 is the date to count.
      Hope this is what you need.

  11. Hi,

    Help me with the count function for a number and a text on the same range. For example, I want to count cells based on attendance, if it is Y, it should be considered, if I update as 1 or 30, it should also be counted for all the data

    • Hi,
      If I understand your task correctly, the following formula should work for you:

      =SUM(COUNTIF(A1:A10,{1,30,"Y"}))

      I hope this will help

  12. Need help. I need to an item in a separate excel spreadsheet to be counted based on two conditions in a worksheet. I need it to count the item once if the Part No of the item (for example is 01667-1" and the order number column is populated with an all numerical number (for examples " 1234" or #1556"). The numerical number is unique except for the first number so I tried using a wild card. Oh and one more thing the numerical column (G:G) may have blanks. The count is populated into a different worksheet.
    I wrote this one but its not working. My result says 0 when I know I currently have 18 items like this....

    =COUNTIFS(' Fruit Config Flow'!$C:$C,"01667-1",' ACCG Config Flow'!$G:$G,"1*")

    Please help!!!

    • Hello!
      The COUNTIFS function uses only range references as criteria_range. Therefore, you cannot use COUNTIFS in your case.
      Try this array formula:

      =SUM(--(FREQUENCY(IF(C2:C10=$K$1,MATCH(REPLACE(G2:G10,1,1,""), REPLACE(G2:G10,1,1,""),0)),ROW(C2:C10)-ROW(C2)+1)>0))

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.
      $K$1 is "01667-1"

      I hope I answered your question. If something is still unclear, please feel free to ask.

  13. Hello, I am trying to write a function that will count employees with either blank termination dates or termination dates after 1/1/20. The other criteria is all of these employees have to be from NYC. The function I have is =SUM(COUNTIFS(Q:Q, {"", ">1/1/2020"}, G:G, "New York")). It only seems to be counting the blanks and not adding term dates post 1/1/20 to the total count. Any advice for resolving would be very appreciated!

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =COUNTIFS(Q:Q, "", G:G, "New York")+COUNTIFS(Q:Q,">1/1/20", G:G, "New York")

      You can learn more about count cells with OR conditions in Excel in this article on our blog.

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

  15. Hello! GREAT ARTICLE, but as a German Excel user there remains a crucial question:

    After

    "Count cells with 2 sets of OR conditions"

    you say:

    "Please note the semicolon in the second array constant"

    Well, semicolon is the standard in Germany for the comma (as in probably all European countries). Can somebody tell me what would be the equivalent for that semicolon then in Germany? Thanks a lot in advance.
    Thanks
    David

    • by the way it is not comma

      • I just got it! I downloaded one of the files on this site where the formula I meant is included - and my German Excel converted it automatically to German enterings. It would be "\" in Europe :-)

  16. Hello,

    Could you help me with a formula -- I have a pack slip for warehousing and I'm trying to sum the total of packages that have either "1" OR "13" items (cell range D10:D20) AND are being shipped via any of the following carriers "GSO", "VINGO", or "UPS" which are listed in cell range C10:C20. I currently have the formula as =SUM(COUNTIFS(C10:C20, {"GSO", "UPS", "VINGO"}, D10:D20, {"=1"; "=13"})) but it'sa not returning the correct results. Help?

    Thanks

    • Hello!
      The formula below will do the trick for you:

      =SUM(COUNTIFS(C10:C20,{"UPS","GSO"},D10:D20,{1,13}))

      or

      =SUM(COUNTIFS(C10:C20,{"UPS","GSO"},D10:D20,{"1","13"}))
      if your numbers are written as text

  17. I have the data I want to add together in column I. I only want to count the data in column I for specific criteria/names in column B.

    I am struggling putting together the formula to achieve this sum for column I based on criteria in column B.

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

  19. Hello.

    I couldn't get the neater "OR" countifs function to work (Formula 2: countifs with array constant). The longer formula 1 (Countif + Countif) does work, but isn't as tidy.

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

    My version is attempting to count the number of people who took a certain course in 2019 OR 2020 (adding the number of delegates from 2019 and 2020 together for a specific course).

    =sum(COUNTIFS('Form responses 1'!B:B,{"2020","2019"},'Form responses 1'!D:D,"Course X")) where column B is the year column, column D is course name (redacted for the purposes of this post)

    However this formula will only return numbers from 2020. Swap the years around, it only returns numbers from 2019. I would like them added together.

    Any ideas what I'm doing wrong?

    • Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      I can assume that in your table ′Form responses 1′!B:B,the year is written not as text, but as a number.
      Therefore, do not use quotation marks - {2020,2019}.

      I recommend using the SUMPRODUCT function for counting. Read more here

      I hope it’ll be helpful.

  20. Hello. Can you please help me figure out how to count how many times a value occurs between two date columns?
    I'm trying to count how many, let say "installs," occur per week with the start at end date columns.
    There is more than one row with start dates and end dates that overlap and I'm trying to break it down by how many are overlapping in each week.
    Column B & C are the start and end of the install. E and F are just part of my model.
    For example, someone may have 44 installs per year, but how many are occurring each week at the same time.
    I'm trying to show how many installs occur/overlap to the right of columns E and F via column G.
    Link to the spreadsheet: https://docs.google.com/spreadsheets/d/1krnLiVUTfXWIWh0PTVXqK9Zpy5lNegYHoUWGMTruI88/edit#gid=997917131
    Here are some formulas I have tried:
    1. =SUMIFS($H$3:$H$44,$G$3:$G$44, >=K3&)+SUMIFS($H$3:$H$44,$G$3:$G$44, "&K2,$A$2:$A$217,$H$2:$H$217,"=K3"},0))*ISNUMBER(MATCH($H$3:$H$44, {"<=L3"},0))*ISNUMBER(MATCH($A$3:$A$44,{"Deb Condon"},0)))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)