Jul
10

How to use Excel COUNTIFS and COUNTIF with multiple criteria

The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more.

Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria.

The difference is that the COUNTIF function is intended for counting cells based on a single condition in one range, while COUNTIFS allows using several criteria and ranges. In practice, however, you can use Excel COUNTIF with multiple criteria as well. So, the aim of this tutorial is to help you figure out the most efficient formula for each particular task.

Excel COUNTIFS function - syntax and usage

As I've already mentioned, the Excel COUNTIFS function is purposed for counting cells across multiple ranges based on one or several conditions. The COUNTIFS function is available in Excel 2013, Excel 2010, and Excel 2007, so you can use the below examples in any Excel version.

COUNTIFS syntax

The syntax of the COUNTIFS function is as follows:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)
  • criteria_range1 - defines the first range to which the first condition (criteria1) shall be applied, required.
  • criteria1 - sets the condition in the form of a number, cell reference, text string, expression or another Excel function, required. The criteria defines which cells shall be counted and can be expressed as 10, "<=32", A6, "sweets".
  • [criteria_range2, criteria2]... - these are additional ranges and their associated criteria, optional. You can specify up to 127 range/criteria pairs in your formulas.

In fact, you don't have to remember the syntax of the COUNTIF function by heart. As well as with any other formula, Microsoft Excel will display the function's arguments as soon as you start typing; the argument you are entering at the moment is highlighted in bold.

The syntax of the Excel COUNTIFS function

Excel COUNTIFS - things to remember!

1. You can use the COUNTIFS function in Excel to count cells in a single range by a single condition as well as in multiple ranges with multiple conditions.

2. Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument).

3. Both contiguous and non-contiguous ranges are allowed.

4. If the criteria is a reference to an empty cell, the COUNTIFS function treats it as a zero value (0).

5. You can use the wildcard characters in criteria - asterisk (*) and question mark (?). See this example for full details.

For the detailed description and syntax of the COUNTIF function, please see this tutorial - Excel COUNTIF function.

How to use COUNTIFS and COUNTIF with multiple criteria in Excel

Below you will find a number of formula examples that demonstrate how to use the COUNTIFS and COUNTIF functions in Excel to handle multiple conditions and ranges.

COUNTIFS and COUNTIF (with multiple criteria) for numbers

By and large, COUNTIFS formulas for numbers fall into 2 categories - counting numbers between the two values you specify, and based on several conditions.

Example 1. Counting numbers between X and Y

You can count how many numbers are between the numbers you specify in two ways - using a COUNTIFS function or a difference between two COUNTIF functions.

COUNTIFS formula: =COUNTIFS(C2:C10,">5", C2:C10,"<10")

This formula counts how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10.
A COUNTIFS formula to count numbers between X and Y

If you want to count cells based on the same condition but including 5 and 10, add "=" to the criteria like this: =COUNTIFS(B2:B10,">=5", B2:B10,"<=10")

COUNTIF formulas to count numbers between X and Y:

  • =COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10") - counts how many numbers greater than 5 and less than 10 are in the range C2:C10. The formula will produce the same result as you see in the screenshot above.
  • =COUNTIF(C2:C10, ">=5")-COUNTIF(C2:C10, ">10") - the formula counts how many numbers between 5 and 10 are in the range C2:C10, including 5 and 10.

Summing up, you use a combination of two COUNTIF functions in the following way. In the 1st function, you count how many numbers are greater than your lower bound value (5 in this example). In the 2nd function, you count how many numbers are greater than the upper bound value (10 in our case). The difference between the first and second number is the result you are looking for.

Example 2. Counting numbers with multiple criteria

Suppose you have a list of products indicating how many items are sold, delivered and in stock. You can use the following formulas to count the numbers of products, at least one item of which has been sold and delivered, i.e. if the values in columns B and D are greater than 0:

=COUNTIFS(B2:B11,">0", D2:D11,">0")
A COUNTIF formula for counting numbers with multiple criteria

Here is one more COUNTIFS formula example with multiple ranges:
=COUNTIFS(B2:B11,"=0", C2:C11,"=0", D2:D11,"=0")

The above formula counts the number of products with zeroes in columns B, C and D. This COUNTIFS formula returns 1 because only Product 5 have "0" in all the three columns.
A COUNTIFS formula for counting numbers in three columns

Note. You do need to use three separate ranges in the formula, one per column, if you want to count the products that contain "0" in all the columns. A simpler formula with a single range =COUNTIFS(B2:D11,"=0") would produce a different result because it returns the total count of cells containing a zero.

Example 3. How to use cell references in COUNTIFS formulas

Of course, you can use a cell reference instead of a number in Excel COUNTIFS formulas. Just remember to enclose the operator in quotes ("") and add an ampersand (&) before the cell reference to construct a text string. For more information about the use of an ampersand in COUNTIF and COUNTIFS formulas, please see Excel COUNTIF - frequently asked questions.

So, let's make a COUNTIFS formula with a cell reference equivalent to the formula from the previous example:

=COUNTIFS(B2:B11,"="&C2, C2:C11,"="&C2, D2:D11,"="&C2)

Cell C2 used in the criteria contains a zero value, so the formula will produce exactly the same result you see in the screenshot above.

Example 4. COUNTIF formulas for non-contiguous ranges

Examples 2 and 3 above demonstrate how to use COUNTIFS in Excel to count cells based on several conditions. Such formulas can be applied both to contiguous or non-contiguous ranges. For example, we have used this formula =COUNTIFS(B2:B11,">0", D2:D11,">0") to find out how many products have a value greater than "0" both in column B and column D.

But what if you want a total count of cells with a certain value in several non-adjacent ranges? For instance, how do you count the total number of zero values in columns B and D? The answer is to use a combination, more precisely, a sum of several COUNTIF functions:

=COUNTIF(B2:B11,"=0") + COUNTIF(C2:C11,"=0")
A COUNTIF formula to count numbers in a non-contiguous range

COUNTIFS and COUNTIF (with multiple criteria) for dates

The COUNTIFS and COUNTIF formulas you use for dates are very much similar to the above formulas for numbers.

Example 1. Count dates in a specific date range

To count the dates that fall in a certain date range, you can also use either a COUNTIFS formula with two criteria or a combination of two COUNTIF functions.

For example, the following formulas count the number of dates in cells C2 through C10 that fall between 1-Jun-2014 and 7-Jun-2014, inclusive:

=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/7/2014")

=COUNTIF(C2:C9, ">=6/1/2014") - COUNTIF(C2:C9, ">6/7/2014")
The COUNTIF formula with 2 conditions to count dates in a specific date range

Example 2. Count dates with multiple conditions

In the same manner, you can use a COUNTIFS formula to count the number of dates in different columns that meet 2 or more conditions. For instance, the below formula will find out how many products were purchased after the 20th of May and delivered after the 1st of June:

=COUNTIFS(C2:C9, ">5/1/2014", D2:D9, ">6/7/2014")
The COUNTIF formula to count dates with multiple conditions

Example 3. Count dates with multiple conditions based on the current date

You can use Excel's TODAY() function in combination with COUNTIF to count dates based on the current date.

For example, the following COUNTIF formula with two ranges and two criteria will tell you how many products have already been purchased but not delivered yet.

=COUNTIFS(C2:C9, "<"&TODAY(), D2:D9, ">"&TODAY())
The COUNTIF formula to count dates with multiple conditions based on the current date

This formula allows for many possible variations. For instance, you can tweak it to count how many products were purchased more than a week ago and are not delivered yet:
=COUNTIFS(C2:C9, "<="&TODAY()-7, D2:D9, ">"&TODAY())

COUNTIFS formulas for text values

In the previous article, I provided an example of Excel COUNTIF formula with two criteria to count the number of cells with 2 different text values in a single range. Now let's see how you can use the COUNTIFS function with multiple criteria and across multiple ranges.

Example 1. COUNTIFS for text values

Suppose, you have a table listing the results of the credits and exams passed by students. What you want to know is how many students have passed all the credits. To do this, you use a usual COUNTIFS formula with several different ranges and the same criteria:

=COUNTIFS(B2:B7,"=P",C2:C7,"=P",D2:D7,"=P",E2:E7,"=P")
A COUNTIFS formula with several different ranges and the same criteria

If you want to know how many credits a particular student has passed or not passed, you can utilize either a COUNTIFS or COUNTIF formula, because this time you need a single range only.

For example, the below formulas will tell you how many credits Bella has passed:

=COUNTIFS(B3:E3,"=P")

=COUNTIF(B3:E3,"=P")
The COUNTIFS formula to count cells with text values in a single range

Example 2. COUNTIFS with wildcard characters

As I mentioned at the beginning of this tutorial, you can use the following wildcard characters in Excel COUNTIFS formulas:

  • Question mark (?) - matches any single character, use it to count cells starting and/or ending with certain characters.
  • Asterisk (*) - matches any sequence of characters, you use it to count cells containing a specified word or a character(s) as part of the cell's contents.
Tip. If you want to count cells with an actual question mark or asterisk, type a tilde (~) before the character in your COUNTIFS formula.

Now let's see how you can use a wildcard char in your real COUNTIFS formulas in Excel. Suppose, you a list of projects assigned to your company's employees. You want to find how many projects are already assigned to someone, i.e. any name is stated in column A. And because we are leaning how to use the COUNTIFS function with multiple criteria, let's add a second condition - the End Date in column D should also be set.

Here is the formula that works a treat: =COUNTIFS(B2:B10,"*",D2:D10,"<>"&""))

Please note, you cannot use a wildcard character criteria with the second range because you have dates rather that text values in column D. That is why, you use the criteria that finds non-blank cells - "<>"&""
The COUNTIFS formula to count entries containing any text in one column and non-blank cells in another column.

Important note! Please remember, when you use an Excel COUNTIFS formula with multiple ranges, all of the ranges must all be the same size, i.e. have the same number of rows and columns. Otherwise, your formula will return the #VALUE! error. This applies to all COUNTIFS formulas in Excel, with any data type - numbers, dates and text.

44 Responses to "How to use Excel COUNTIFS and COUNTIF with multiple criteria"

  1. Michael says:

    Can COUNTIFS can be used to find the number of matches within the same range?

    I have a range of 500 postal codes in one column. These are formatted in two groups of three characters each (i.e. A9A 9A9). The first group of characters will change slightly to represent a different region - A9B, A9C, A9D, etc. I would like to get a count of each regional group.

    I could do this over and over again (as I've already done) as follows:
    =COUNTIF(M1:M500, "A9A*")

    This proved to be very repetitive to copy, paste and modify this 25 times for each of the regional postal code categories I'm working with. Can COUNTIFS do this at one time?

    • Hello Michael,

      Regrettably, COUNTIFS cannot help in your case because it counts cells that meet ALL of the criteria you specify in the formula. You can achieve the desired result in this way:

      Create an additional column and copy the following formula there =LEFT(A2,3) where A is your postal codes column. This formula will extrat the first 3 characters of the codes.

      Then you can proceed in 2 ways.

      Way 1:
      - Sort your table by the postal code column or the newly created column with the above formula.
      - Apply subtotal to the table (Data >Outline >Subtotal) with these settings:
      At each change in : Column with the formula
      Use function: Count
      Add subtotal to: Column with the formula
      - Click Ok.
      - Then Press Number 2 at the left side of you sheet and you will get the count of codes by region.

      For more info about using subtotals please see this article - Using Subtotals in Microsoft Excel

      Way 2:
      - Insert a pivot table (select your table and go to Insert > Tables > PivotTable).
      - Place your pivot table onto a new sheet.
      - Drag and drop the column with the formula to the Rows section and the original column with the postal codes to the Values section.

      You will get the same count of codes by region.

  2. Michael says:

    Thank you. I just spoke to a colleague who walked me though a pivot table of my data. Thanks again.

  3. Emily says:

    Hello,

    I have a list of classes with the day and start & end times in one spreadsheet.

    The format looks like this:
    M 8:00 AM - 9:50 AM
    T 12:30 PM - 2:20 PM
    M 9:00 AM - 10:15 AM
    F 7:00 AM - 1:00 PM

    In another spreadsheet i have a table like this one below. I want to know how many classes occur on Mondays between 6:00 am and 6:29 am,6:30 am - 6:59 am,7:00 am - 7:29 am, etc.

    M T W R F
    6:00 AM
    6:30 AM
    7:00 AM
    7:30 AM
    8:00 AM
    8:30 AM
    9:00 AM
    9:30 AM
    10:00 AM
    10:30 AM
    11:00 AM
    11:30 AM
    12:00 PM
    12:30 PM
    1:00 PM
    1:30 PM
    2:00 PM
    2:30 PM
    3:00 PM
    3:30 PM
    4:00 PM
    4:30 PM
    5:00 PM
    5:30 PM
    6:00 PM
    6:30 PM
    7:00 PM
    7:30 PM
    8:00 PM
    8:30 PM
    9:00 PM
    9:30 PM
    10:00 PM
    10:30 PM
    11:00 PM
    11:30 PM
    12:00 AM

    I appreciate your help tremendously!

    Thank you!

  4. Emily says:

    oops, it's not pasting like i want it to. basically, each day is a column, each time is a row. sorry for the confusion.

  5. New Excel user says:

    Trying to find a formula that will average only if EACH cell has a value greater than 0 in it; or if the last 3 consecutive months have a value greater than 0. Any help is greatly appreciated.

    • I believe, you can use a formula similar to this:

      =IF(AND(E2>0,F2>0,G2>0),AVERAGE(A2:E2),"-") where E, F and G are the last 3 months' values. You can add more > operators to the nested AND function if you want to check more than 3 months.

  6. rodz says:

    Hi,
    how can i automatically count or computed in Monday to Sunday data base count as week summary

  7. Glenn Campbell says:

    Column G is "Barcodes". Column J is "Carriers". How can it get the Excel to display "Flexsteel Truck or FedEx or UPS" in column J:J whenever a BX number (for example..BX09225)is enter in G:G?

    Thanks in advance,

    Glenn

    • Hi Glenn,

      If you want to use only formulas, then enter this one in cell J2:
      =IF(And(G2<>"", len(G2)>2,left(G2,2)="BX"),"Flexsteel Truck or FedEx or UPS", "")

      Then copy the formula across other cells in column J.

      If you want to insert some values in column J, but have these values replaced with "Flexsteel Truck or FedEx or UPS", or something like that, as soon as the code appears in column G, you need a special VBA macro to fulfill this.

  8. Kees Struik says:

    Help please.
    3 columns. Column A is a number (Day), Column B & C are time.
    Count rows where day number is less than 6 and times in either B or C are between 5:00 & 7:00

    -----------
    A B C
    D T0001 T0002
    1 6:14
    1 6:57
    1 6:58
    1 20:50
    1 23:05
    2 17:47
    2 17:59
    3 6:01
    3 17:46
    5 18:20
    6 7:06
    6 11:50
    6 13:29
    6 13:53
    6 14:57
    6 17:36
    7 10:26
    7 10:32

  9. Kees Struik says:

    Column B & C data not pasted very well. Just put every second time in column c.

    • Hello Kees,

      I believe the following formula will work a treat:
      =COUNTIFS(A2:A10,"<6",B2:B10,">="&TIME(5,0,0),B2:B10,"<="&TIME(7,0,0))+COUNTIFS(A2:A10,"<6",C2:C10,">="&TIME(5,0,0),C2:C10,"<="&TIME(7,0,0))

      In your case, the result will be 4.

  10. Jason Pisani says:

    Hi Svetlana I sent you an email for your assistance could you be so kind to help me? thanks Jason

  11. gio bitoy says:

    Sex Age
    F 14
    F 19
    F 15
    F 14
    F 16
    F 21
    F 24

    how can i count with these limitations? what formula/syntax will i use?
    i need to count all F with age less than 15, F equal to 15 but not more than 19 and all the F equal 20 but not more than 24

    I want to tabulate it in this format.

    F less than 15
    F equal 15 pero not more than 19
    F equal 20 but not more than 24
    thanks for helping me on this one

    • Hi Gio,

      Try these formulas:

      F is less than 15:
      =COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,"<15")

      F equal 15 pero not more than 19:
      =COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,">=15",$B$2:$B$8,"<19")

      F equal 20 but not more than 24:
      =COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,">=20",$B$2:$B$8,"<24")

  12. Brad MacNamara says:

    I am trying to add a value for a Alpha value in a cell. The Cell can have an M=5, a C=3 or a Z=1 so in the same cell I want to show a value of the corresponding letter, with a default of "0" if they do not choose either letter.
    Also I am adding another value in a subsequent cell for "C" a value of 1,2,3 will be used as as a negative of 1=-0.5, 2=-1 and 3=-1.5, same values only apply to a Z 4=-.5, 5=-1, and 6=-1.5.

    Thanks for your help in advance...

  13. vcoolio says:

    Hello Svetlana,

    I noticed that in your tutorial that COUNTIF formulas are used outside the columns/cells that need to be totalled.

    I have a work sheet with a number of columns, one of which is Sales/Purchases. The data in the work sheet is extensive and the columns are very long so I like to keep track of the number of Sales and Purchases for a month by using =COUNTIF(range,"SALE")and the same for Purchases. If I place the formulas in a cell at the top or bottom of this column everything works fine. However, should I place the formulas outside this column all I get is a date (such as January 1900). Am I doing something wrong or is the formula restricted to its specific column?

    Thank you in advance for any help.

    Cheers,
    vcoolio.

    • Hello Vcoolio,

      You can put your formulas in any cells. For the formulas to work correctly, try the following:

      1 Apply the General format to the cell with the formula (press Ctrl + 1 and select General in the Format Cells dialog window).
      2 If you used relative cell references in the range argument, the references got distorted when you copied the formula. Try using the absolute cell references instead (e.g. $B:$B for column B).

      If the above suggestions do not work, please send me a sample workbook at support@ablebits.com and we will try to figure out the source of the problem.

  14. vcoolio says:

    Dear Svetlana,

    Thanks for that. I've sorted it out. Someone had been clearing cells by pressing the space bar and obviously a space is still a character so this was distorting any new entries such as formulas. I've made a note of your resolutions above for future reference. Excellent stuff!

    Your help is much appreciated (and is worth a vodka or two!!).

    Cheers,
    vcoolio.

  15. Brandon says:

    Hi, I have a range of cells I want to count for a skills matrix for my staff. The ranges of what I want counted are drop down menus in each of the cells with "Sound", "Advanced", "Expert" etc, and these I wanted counted in the number of times that the cells have one of those words in them. The other cells contain "UnAware" and "Basic" which I dont want counted in the original total, but put in a total for all the types of details entered, this way I will get a % of staff with the desired level of training/competence in the processes they use every day.
    I tried COUNTIFS but I kept getting a zero count even though I used the formula correctly (no error message). Could you please help me work this out?

  16. Mr Sunny says:

    Hi Svetlana,
    Could you be kind enough to help me out on this excel problem I am trying to refer to a particular cell for formatting but there is a conflict. I will appreciate your prompt response.
    I have sent the file to your mail.
    Thanks

    • Hi!

      In your case, it probably makes sense to look up by 2 columns C and D using an array INDEX / MATCH formula. I've sent you an email with a formula example, hopefully that is the result you are trying to achieve.

  17. Angela says:

    Can I use countifs with more to count more than three ranges and criteria?

  18. Shad says:

    Hi Svetlana,

    How can I count two different texts in one column?

    So my usual formula is: =COUNTIFS(C:C,"Passed",D:D,"Quebec")
    And its giving my correct figures.

    But if in the column C, i also want to count any other status that says Remediation Passed, how should I say that? No change in column D. I tried something like, COUNTIFS(C:C,"Passed" OR "Remediation Passed",D:D,"Quebec"), but that didn't work.

    Grateful for your insights.

    Cheers

  19. Tim says:

    Hi Svetlana,

    I was trying to follow your tutorial and adapt it to my data, but it didn't seem to work. Would you mind helping me troubleshoot please? :)

    I was trying to create a COUNTIF for instances when there's text in column A AND text in column C OR column D.

    Thanks in advance!

  20. katy says:

    Hiya

    I have a workbook with mulitple sheets and an overview sheet.
    The overview has validation list for different months whicg relates to each of the muliple sheets.

    I need a count if where by the sheet name in the range will change depending on the month selected in the validation

    i.e If 'May-14' in cell 'Overview!'C2 - Countif('May-14'!R:R,Criteria)
    If then in overview cell2 it is changed to Jun-14 the Countif will change to Countif('Jun-14'!R:R,Criteria).

    Please help?!

  21. KC says:

    Hi

    I have two columns named as Reportable (with Yes or No) and another column named Responsible_Officer.

    I am trying to do a number count for each yes per each individual responsible officer.

  22. Ansu says:

    Hi,
    Help needed on the formula for getting the desired count as shown in the table.
    Need to get count as 1 when either or both the parts are 100% completed.

    Name Part Status Desired Count
    A LHS 100.0% 1
    A RHS 100.0%

    B LHS 100.0% 1
    B RHS

    C LHS 1
    C RHS 100.0%

    D LHS 0
    D RHS

    Thanks a lot in advance.

  23. Mark says:

    I am trying to tally the number of calls by day-of-the-week each person has from a table that looks like this but for the entire year:

    JANUARY Sa Su Mo Tu We Th Fr
    James call
    John call call
    Mike call
    Jane call call

    FEBRUARY Sa Su Mo Tu We Th Fr
    James call call
    John call call
    Mike call
    Jane call call

    MARCH Sa Su Mo Tu We Th Fr
    James call
    John call call
    Mike call call
    Jane call

    I have a table at the top of the spreadsheet that shows the results and looks like this:

    Sa call Su call Mo call etc
    James ?? ?? ??
    John ?? ?? ??
    Mike ?? ?? ??
    Jane ?? ?? ??

    What formula do I use that references the names in the left column with the rows to give me the ?? tallies based on day of the week when they occur? Thanks.

  24. Pagz says:

    How do I use countif to meet the same criteria but across multiple ranges in multiple worksheets?

  25. Jerrie Mar says:

    Hi Svetlana,

    this is my problem

    A1 B1 C1
    6:00 - 14:00 14:00 - 20:00 20:00 - 6:00
    A2 = B2 = C2 =
    A3 = B3 = C3 =

    for example, i have a value in different excel sheet.
    if i enter 8:00 am there, it will be automatically appear in A2.
    B2 and C2 will be blank.
    but if i put the value 15:00 pm, it will be automatically appear in B2. A2 and C2 will be blank.

    Hope you can help me with this.. Thanks

  26. Jerrie+Mar says:

    OR

    A1 is 6:00 AM
    B1 is 14:00 PM

    A2 is 15:00 PM

    B2 =A2 (This should not appear in the range of A1-B1) it will leave blank

  27. Jix says:

    First Column 2nd column
    List =LST what is the formula of getting a shortcut e.g i type
    Cable List next column will automatic appear LST and so and so fort
    Wire Thanks awaiting your prompt response.

Post a comment



Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard