The COUNTIFS function syntax has the following arguments: criteria_range1 Required. The first range in which to evaluate the associated criteria. Criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, '>32', B4. Jan 29, 2016 - The COUNTIFS function in Excel counts the number of cells in a range that match a set of multiple criteria. COUNTIFS extends the COUNTIF.

You use the COUNTIF function in Excel when you want to count cells based on a single criterion. If you want to count based on multiple criteria, you will use the COUNTIFS function. Here is how to make the best use of this function.

Using COUNTIF with multiple criteria

Syntax for a single criteria is :

=COUNTIF(range, criteria)

The best function you can use for situations when you need to count cells based on multiple criteria is the COUNTIFS function. The “s” on the end of Countif makes it plural, and that means that there are multiple criteria.

You can use up to XX criteria_range and criteria pairs in a single COUNTIFS function, each criterion ranges one MUST be the same shape.

You should note that the COUNTIFS function takes all of the criteria_range/criteria pairs and counts only those instances where all of the pairs are true.

COUNTIFS formula with the same criteria

If you want to count items with identical criteria, you still need to supply each criteria_range / criteria pair individually.

For example, here is the right formula to count items that have 0 both in column B and column C:

=COUNTIFS($B$2:$B$7,'=0', $C$2:$C$7,'=0')

This COUNTIFS formula returns 1 because only “Manuel” have “0” value in both columns.

If you use simple formula with a single criteria_range like COUNTIFS(B2:C7,”=0″) would show a different result – the total count of cells in the range B2:C7 containing a zero (which is 4 in this example).

COUNTIFS with two criteria in different criteria ranges

When you understand how to use COUNTIFS with one critera_range and criteria, then it becomes easy to extend it to two or more criteria_range/criteria combinations.

For example, let’s count the number of times the combination of Start and Finish appear in our spreadsheet :

=COUNTIFS(B3:B14,'Start',C3:C14,'Finish')

As you can see, this formula has returned the correct number of rows where both criteria are matched in their respective columns.

It’s worth repeating one of the rules of COUNTIFS here – the two criteria ranges must be the same shape and size or your formula won’t work, and will return a #VALUE error. Here’s an example where the second criteria range is smaller than the first:

=COUNTIFS(B2:B13,'Start',C2:C12,'Finish')

COUNTIFS with two criteria in the same criteria range

Another way to use COUNTIFS is to use the same criteria with more than one criteria_range. For example, suppose we want to count the number of times either Start or Progress appears in the Product column. You can try this formula using COUNTIFS:

=COUNTIFS(B2:B13,'Start',B2:B13,'In Progress')

Unfortunately, this formula will return a value of 0, as shown here:

Common errors with COUNTIFS

This is a common mistake that even experienced Excel users make when using COUNTIFS (and its counterpart, SUMIFS which you can learn about here).

The problem is that COUNTIFS looks in our criteria_range, B2:B13 and tries to count cells that contain both Start and Progress. Remember that we wanted to count cells that contain either Start or Progress. Of course, there are no cells in our range that contain both Start and Progress, so the result is 0.

Ironically, the best way to find the answer in this scenario is so simple that it is often overlooked – just use two COUNTIF functions in your formula, rather than once COUNTIFS function, like this:

=COUNTIFS(B2:B13,'Start')+COUNTIF(B2:B13,'Progress')

In other words, we simply count all the rows that contain Start, and add those to the tally of rows that contain Progress.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

See 7 ways to count in Excel. Examples and video tutorials show how to count Excel cells with numbers, text, blanks, or cells that contain specific words or other criteria

Overview: 7 Ways to Count in Excel

To see a quick overview of 7 ways to count in Excel, watch this short slide show, or see the steps for using each method, in the video below.

Download the 7 Ways to Count sample workbook, so you can follow along with the video.

Count Numbers, All Data, or Blank Cells

This video shows the COUNT function, the COUNTA function, and the COUNTBLANK function, and warns of the quirks in counting some types of cells.

Written instructions are below the video.Count Cells with Numbers, Count Cells with Data and Count Blank Cells. Also, download the sample workbook, to see all the formulas.

Count Cells with Numbers -- COUNT

The COUNT function will count cells that contain numbers. Its syntax is:
=COUNT(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the COUNT formula.

The following COUNT function example uses one argument -- a reference to cells A1:A5.

1. Enter the sample data on your worksheet
2. In cell A7, enter an COUNT formula, to count the numbers in column A: =COUNT(A1:A5)
3. Press the Enter key, to complete the formula.
4. The result will be 3, the number of cells that contain numbers.
   Cell A1 isn't counted, because it contains text.

Note: Since dates are stored as numbers, the COUNT function will include any cells that contain dates.

Count Cells with Data -- COUNTA

The COUNTA function will count cells that are not empty. (See note on blank cells below)

Its syntax is:
=COUNTA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the formula. The following example uses one argument -- a reference to cells A1:A5.

1. Enter the sample data on your worksheet
2. In cell A7, enter a COUNTA formula, to count the numbers in column A: =COUNTA(A1:A5)
3. Press the Enter key, to complete the formula.
4. The result will be 4, the number of cells that contain data.

Count Blank Cells -- COUNTBLANK

The COUNTBLANK function will count cells that are empty. (See note on blank cells below)

Its syntax is:
=COUNTBLANK(range).
The following example uses a reference to cells A1:A5.

1. Enter the sample data on your worksheet
2. In cell A7, enter a COUNTBLANK formula, to count the numbers in column A: =COUNTBLANK(A1:A5)
3. Press the Enter key, to complete the formula.
4. The result will be 1, the number of empty cells.

Cells That Look Blank

Both COUNTA and COUNTBLANK will count cells with formulas that look empty, if the formula result is an empty string {'). The empty string is treated as text, and counted.

For example, =IF(B2=',',B2).

Both COUNTA and COUNTBLANK will also count cells which had formulas that returned an empty string, but then were converted to values, by copying and pasting as values.

• NOTE: You can see apostrophes in those 'non-blank' converted cells, if you add a check mark to Excel Options, Advanced, Lotus Compatibility, 'Transition navigation keys.'

To fix cells that look blank, but are being counted, see my Contextures blog: Fix Blank Excel Cells Copied From Database▲TOP

Count cells that match one criterion

This video shows how to use the COUNTIF function to count cells that contain a specific string of text, such as 'Pen'. You can also find text that is part of a cell -- how many orders were placed for any kind of pen, such as 'Gel Pen', 'Pen' or even a 'Pencil'?

The written instructions are below the video: Match criterion exactly and Match criterion in a string

Also, be careful, because you might get False Duplicates With COUNTIF , and COUNTIF does not count specific numbers within other numbers. And to count specific text, without counting similar text, see the example here.

Match one criterion exactly -- COUNTIF

In Excel, count cells that meet a specific criterion. In this example only the Pen orders will be counted.

1. Select the cell in which you want to see the count (cell A12 in this example)
2. Type an equal sign (=) to start the formula
3. Type: COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
5. Type a comma, to separate the arguments
6. Type the criterion. In this example, you're checking for text, so type the word in double quotes: 'Pen'
   Note: upper and lower case are treated equally
   
7. Type a closing bracket
   The completed formula is: =COUNTIF(A1:A10,'Pen')
   
8. Press the Enter key to complete the entry
9. The result will be 4, the number of cells that contain 'Pen' ▲TOP

Match criterion in a string

In Excel, count cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be counted, because they contain the string 'pen'.

1. Select the cell in which you want to see the count (cell A12 in this example)
2. Type an equal sign (=) to start the formula
3. Type: COUNTIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
5. Type a comma, to separate the arguments
6. Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters: '*Pen*'
   Note: upper and lower case are treated equally
   
7. Type a closing bracket
   The completed formula is: =COUNTIF(A1:A10,'*Pen*')
   
8. Press the Enter key to complete the entry
9. The result will be 6, the number of cells that contain the string, 'Pen' ▲TOP

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 7 above could be changed to:
=COUNTIF(A1:A10,'*' & B12 & '*')
if cell B12 contained the text -- pen.

False Duplicates With COUNTIF

Be careful when using COUNTIF, because it matches numbers, with text that looks like numbers, and that could result in false counts.

For example, if you have a text entry -- '00123' -- it would be counted as a duplicate for the number -- 123.

=COUNTIF($B$2:$B$10,B2))>

If your data could contain entries like that, use SUMPRODUCT, instead of COUNTIF.

=SUMPRODUCT(--($B$2:$B$10=B2))>1

Count Numbers in Numbers

The COUNTIF function can find specific letters or numbers in a text string. However, COUNTIF cannot find a specific number within a real number.

In the screen shot below, there are 4 items in the list that contain a '1'. The COUNTIF formula in cell A9 gives an incorrect result of 3. It does not count the '1' in cell A2, because that cell contains a real number, not a text string.

Use FIND or SEARCH

Although the COUNTIF function can't count specific numbers within real numbers, the FIND or SEARCH function will locate them.