Count with Column Criteria

How to count items in one worksheet column, based on values in a different column. For example, count all orders with a note in Problem column, but only if Region is "East", using Excel COUNTIFS function.

Excel COUNTIFS Function

In Microsoft Excel 2007 and later versions, you can use the COUNTIFS function to count rows that meet two or more criteria.

COUNTIFS Arguments

With the COUNTIFS function syntax, there are 2 required arguments:

1. criteria_range1 - cells to check for criteria
2. criteria1 - criteria to match

Optional Criteria Arguments

If needed, you can add more pairs of criteria ranges and criteria in the COUNTIFS function.

For example,

• [criteria_range2] - second range of cells to check for criteria
• [criteria2] - second criteria to match
• [criteria_range3] - third range of cells to check for criteria
• [criteria3] - third criteria to match

In the screen shot below, the criteria_range2 argument is highlighted.

The square bracket indicates that the argument is optional

Criteria Range Rules

When adding each set of criteria range and criteria in the COUNTIFS function, you must follow these two rules:

1. All criteria ranges must be the same size (same number of rows and columns)
2. Each criteria range must be a contiguous block of cells

COUNTIFS - Text and Numbers

This short video shows how to count items that have specific text in one column, and numbers over a set amount in a different column. The formula uses the COUNTIFS function, available in Excel 2007 and later.

There are written steps below the video, and a full transcript for the video is at the end of this page.

Count with Multiple Criteria - Text and Numbers

In this example only the rows where the item is "Pen" and the quantity is greater than or equal to ten will be counted.

1. Select the cell in which you want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   COUNTIFS(
4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
5. Type a comma, and the first criterion:   "Pen"
   Note: Because this is a text criterion, it is enclosed in double quote marks.
   
6. To start the next set of criteria, type a comma
7. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
8. Type a comma, and the second criterion:  ">=10"
   Note: Because this criterion includes operators, it is enclosed in double quote marks. To count rows where the quantity is equal to 10, only the number 10 would be required.
   
9. Finish with a closing bracket: )
10. The completed formula is shown below.
    
11. Press the Enter key to complete the entry

The result shows the number of rows that meet the criteria

Note: Instead of typing the criterion in a formula, you can refer to a cell, as shown in the second formula below. If using operators, enclose them in double quote marks.

Use typed criteria, to get the count of cells:

• =COUNTIFS(A2:A10,"Pen",B2:B10,">=10")
  

or cell references:

• =COUNTIFS(A2:A10,D3,B2:B10,">=" & E3)

Count Text and Not Blank

In this example, there is a list of orders, and we want to count the orders, based on 2 criteria, stored in two different columns:

• Region is East (column B)
• Problem has a note in cell (column D)

In the screen shot below, the orders in rows 2 and 9 meet both of the criteria:

COUNTIFS Formula

To get the count of problem orders in the East, enter this formula in cell F5:

=COUNTIFS(B2:B11,F2,D2:D11,”<>”)

The formula result is 2, and it uses two sets of arguments to get the total count:

1. Criteria set 1
   • check the Region names in criteria range 1 -- B2:B11
   • look for our Region criteria -- the region name in cell F2
2. Criteria set 2
   • check the Problem notes in criteria range 2 -- D2:D11
   • look for our Problem criteria -- cells that are not empty -- "<>"

NOTE: The criterion “<>” is the “not equal to” operator. Used alone it means “not equal to ‘no text'”, so it will count cells that are not empty.

That formula result matches the manual count that we did earlier.

Note: Count Blank or Empty String

In the Region/Problem example, a few notes were typed in the Problem column, and the remaining cells were empty. Our formula only counts the cells that are not empty.

However, if column D contained formulas, and some cells had a result of "" (an empty string), those cells would be also counted as “not empty”, just like the cells that contain text, even though they look blank.

Be sure that your blank cells are really empty, if you’re going to use this formula.

If not, instead of COUNTIFS, you could use a SUMPRODUCT formula, like this one:

• =SUMPRODUCT(--(B2:B11=F2),--(D2:D11<>""))

NOTE: Those are two minus signs (double unary) before each section of the SUMPRODUCT formula, not long dashes.

Multiple Criteria in One Column

In the above formulas, the criteria that you want to count are in 2 different columns, like the "Pen" and "Quantity" example above.

However, for some data, you might need to check the same column multiple times, like the example in this video.

This video shows how to use the COUNTIFS function to count cells based on a range of numbers.

In this video, the minimum and maximum numbers are entered on the worksheet.

In cell E6, a COUNTIFS formula counts the number of cells, in column B, that meet both of the following criteria

• number in cells B2:B10 is greater than or equal to the Minimum number, entered in cell E4
• number in cells B2:B10 is less than or equal to the Maximum number, entered in cell G4

Here is the formula in cell E6:

• =COUNTIFS($B$2:$B$10,">=" & E4, $B$2:$B$10,"<=" & G4)

Note: There is a full transcript for the video at the end of this page.

COUNTIFS Function Warnings

There are a few things to be careful with, when using the COUNTIFS function:

• Count might include false duplicates
• Specific numbers within other numbers are not counted - use other functions
• Cannot count text strings longer than 255 characters
• Similar text might be counted, instead of just specific text - see the example here

7 Ways to Count in Excel

In addition to COUNTIFS, there are many other ways to count in Excel. To see a quick overview of 7 ways to count in Excel, watch this short video. And get the 7 Ways to Count sample workbook, so you can follow along with the video.

Also, tThere are more counting formula examples and sample files on the Excel Count Functions page.

Change Operator for COUNTIFS Function

Instead of typing the operator into the COUNTIFS formula, as shown above, you can create a list of all possible operators, and select one from a drop down list. Then, refer to that operator in the formula.

This video shows the steps for setting up the formula, and the written instructions are on the Excel Count Functions page.

The example in the video uses COUNTIF, and you can follow the same steps to set up the drop down list of operators for the COUNTIFS function.

Video Transcript: Text and Number Criteria

This is the full transcript for the video, "Count Based on Text and Number Criteria", shown above on this page.

--------------------

In Excel, you can count using criteria with the COUNTIF function. In later versions of Excel, 2007 and later, you can count multiple criteria with the COUNTIFS function.

Here we have the list of items that we've sold, and the quantity for each. We would like to find the number of orders where a pen was the item sold, and the quantity is greater than 10.

Start the Formula

In this cell, I'm going to start with an equal sign, and then type COUNTIFS, and open bracket

The first thing I'm going to check is the item that was sold. The range, the first range is A2 to A10.

Then I'll type a comma, and the criteria for that range, I'm just going to type in here, inside double quotes, pen, and then another comma. So that's the first thing we're going to check is what item was sold.

Add 2nd Criteria

And the next will be the quantity. I'll select the range that has the quantities, another comma.

We want quantity greater than, or equal to 10, so within double quotes I'll do a greater than symbol, equal, and a 10. Then another double quote, close the bracket and press Enter.

There were 2 orders for pen, where the quantity is greater than 10.

Refer to Worksheet Cell

Instead of typing these criteria in here, I can refer to a cell. So instead of typing pen, inside double quotes, I could click on a cell where I have typed the word pen.

The same for this criteria, for the quantity. I'm going to take out the 10, just by deleting that, leaving the operators within the double quotes.

Then I'll type an ampersand, and the cell that has the number. So this is greater than or equal to whatever number is in cell E3.

Change Criteria on Worksheet

When I press Enter, I get the same result, but it's just easier to change then.

I could type a 5 here now, and we see that there were 4 orders where the quantity is greater than or equal to 5, instead of the 10 that we had in there before.

So this formula is much more flexible if you use cell references, rather than typing the values in as hard coded values.

Video Transcript: Count Numbers in a Range

This is the full transcript for the video, "Count Numbers in a Range", shown above on this page.

--------------------

In Excel 2003 and earlier versions, if you want to count things based on criteria you can use COUNTIF

In Excel 2007 and later versions, there's also a new function called COUNTIFS and with this you can do some things a little more easily.

So, if we wanted to count items that have quantity that's between 5 and 10, in the old version we had to combine two different COUNTIFs

Start the Formula

To get our answer with COUNTIFS, we create one formula, and we can put different sets of ranges and criteria

We can have up to 127 of those combinations, so we get quite a bit of flexibility.

In this case, we've got COUNTIFS, where we're looking at this range of cells, and saying, I want:

• quantities that are greater than or equal to the number in E4
• AND, they also have to be less than or equal to the number that's in G4

So we've combined the two criteria

And when we press Enter, we get 6 as a result

Add More Criteria

We can add more criteria as well, and in this case, we could look for items where there's a pen in the order.

So I've added another set of criteria, so the third set is looking at range A2 to A10, and finding items where there's a pen.

And when we press Enter, there are only 2 now, where

• the quantity is between 5 and 10
• AND the item sold was a pen

Use Wildcards

You can also use wildcards, and in this formula, instead of just typing "pen", we typed "pen*", with an asterisk. So we get anything that starts with "pen"

So now there are 4, because we're getting "pen" and "pencil" items, where the quantity is between 5 and 10

Get the COUNT Sample Files

• Count With Criteria: To see the examples from this tutorial, get the Count Items With Criteria sample file. The zipped file is in xlsx format, and does not contain any macros.
• 7 Ways to Count: Download the 7 Ways to Count sample workbook, to follow along with the 7 Ways to Count video, above. The zipped file is in xlsx format, and does not contain any macros.

More Function Tutorials

More Count Functions

Count Cells With Specific Text

SUM / SUMIF

Subtotal Feature