Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
In its simplest form, COUNTIF says:
- =COUNTIF(Where do you want to look?, What do you want to look for?)
For example:
- =COUNTIF(A2:A5,'London')
- =COUNTIF(A2:A5,A4)
COUNTIF(range, criteria)
Argument name | Description |
|---|---|
range (required) | The group of cells you want to count. Range can contain numbers, arrays, a named range, or references that contain numbers. Blank and text values are ignored. Learn how to select ranges in a worksheet. |
criteria (required) | A number, expression, cell reference, or text string that determines which cells will be counted. For example, you can use a number like 32, a comparison like '>32', a cell like B4, or a word like 'apples'. COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria. |
Examples
To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.
Data | Data |
|---|---|
apples | 32 |
oranges | 54 |
peaches | 75 |
apples Uncharted PC Installer is an application that will download and install the full version of the game Uncharted: Drakes Fortune on your computer. The program will download the game with the maximum speed of your connection, after full installation process with just a few clicks to our installer loaded Crack to the game.  | 86 |
Formula | Description |
=COUNTIF(A2:A5,'apples') | Counts the number of cells with apples in cells A2 through A5. The result is 2. |
=COUNTIF(A2:A5,A4) | Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1. |
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3) | Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function. |
=COUNTIF(B2:B5,'>55') | Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2. |
=COUNTIF(B2:B5,'<>'&B4) | Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,'<>75'). The result is 3. |
=COUNTIF(B2:B5,'>=32')-COUNTIF(B2:B5,'>85') | Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (<) or equal to (=) 85 in cells B2 through B5. The result is 3. |
=COUNTIF(A2:A5,'*') | Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4. |
=COUNTIF(A2:A5,'?????es') The mp3 format music files in the 'Northwest Fiddle Field Recordings'. This will bring up a window asking you the folder to which to save the file and the name.  | Counts the number of cells that have exactly 7 characters, and end with the letters 'es' in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2. |
Common Problems
Problem | What went wrong |
|---|---|
Wrong value returned for long strings. | The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters. To match strings longer than 255 characters, use the CONCATENATE function or the concatenate operator &. For example, =COUNTIF(A2:A5,'long string'&'another long string'). |
No value returned when you expect a value. | Be sure to enclose the criteria argument in quotes. |
A COUNTIF formula receives a #VALUE! error when referring to another worksheet. | This error occurs when the formula that contains the function refers to cells or a range in a closed workbook and the cells are calculated. For this feature to work, the other workbook must be open. |
Best practices
Do this | Why |
|---|---|
Be aware that COUNTIF ignores upper and lower case in text strings. | Criteria aren't case sensitive. In other words, the string 'apples' and the string 'APPLES' will match the same cells. |
Use wildcard characters. | Wildcard characters —the question mark (?) and asterisk (*)—can be used in criteria. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the character. For example, =COUNTIF(A2:A5,'apple?') will count all instances of 'apple' with a last letter that could vary. |
Make sure your data doesn't contain erroneous characters. | When counting text values, make sure the data doesn't contain leading spaces, trailing spaces, inconsistent use of straight and curly quotation marks, or nonprinting characters. In these cases, COUNTIF might return an unexpected value. Try using the CLEAN function or the TRIM function. |
For convenience, use named ranges | COUNTIF supports named ranges in a formula (such as =COUNTIF(fruit,'>=32')-COUNTIF(fruit,'>85'). The named range can be in the current worksheet, another worksheet in the same workbook, or from a different workbook. To reference from another workbook, that second workbook also must be open. |
Note: The COUNTIF function will not count cells based on cell background or font color. However, Excel supports User-Defined Functions (UDFs) using the Microsoft Visual Basic for Applications (VBA) operations on cells based on background or font color. Here is an example of how you can Count the number of cells with specific cell color by using VBA.
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
See also
Count | Countif | Countifs | Sum | Sumif | Sumifs
The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.
Count
To count the number of cells that contain numbers, use the COUNT function.
Countif
To count cells based on one criteria (for example, greater than 9), use the following COUNTIF function.
Note: visit our page about the COUNTIF function for many more examples.
Countifs
To count cells based on multiple criteria (for example, green and greater than 9), use the following COUNTIFS function.
Sum
To sum a range of cells, use the SUM function.
Note: visit our page about the SUM function for many more examples.
Sumif
To sum cells based on one criteria (for example, greater than 9), use the following SUMIF function (two arguments).
Which Of The Following Is The Correct Argument(s)/syntax For The Countifs Function
To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).
Note: visit our page about the SUMIF function for many more examples.
Sumifs
To sum cells based on multiple criteria (for example, circle and red), use the following SUMIFS function (first argument is the range to sum).
General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average cells based on one or multiple criteria.
I an looking to find a value from countifs ,using an array of dates and an array of names.
'Shout out to Jeeped for providing the code for date array below.'
The result is always zero, and 'I think?' its because both of the arrays are of different types? or maybe format is a better word.
I can use the dts array in similar countifs ..see below.
however as soon as I need to introduce another array for say 'names', the countifs line will execute and the code runs, but. just returns zero.
How can I create an array of 'names' that would work with the dts array in a countifs situation?
Thanks!
Edit 4/2/2018 1:00pmAdding Example to show that changing the order still returns 0.
within worksheet use this, or re-order for the other line commented out in the code below.enter image description hereThen using either the active line of the one commented out.
****The answer should be 2
*Date To be within 2018 Or Blank from dts Array*a Column to contain 'Z'*a Column to contain a name from name Array
Ben.Name
Ben.NameBen.Name
1 Answer
Documentation for the
WorksheetFunction commands isn't great. It's a good idea to refer to the documentation for the related worksheet function. (ie., COUNTIFS vs Application.WorksheetFunction.Countifs)Looks like your added criteria are out of order.
COUNTIFS Syntax
The
COUNTIFS function syntax has the following arguments:criteria1(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 as32,'>32',B4,'apples', or'32'.[criteria_range2, criteria2,] ..(Optional) Additional ranges and their associated criteria.
Up to127range/criteria pairs are allowed.
Important:
- Each additional range must have the same number of rows and columns as the
criteria_range1argument. The ranges do not have to be adjacent to each other.
More Information:
- MSDN : WorksheetFunction.CountIfs Method (Excel)
- Office.com : COUNTIFS function (with video)
13.4k4 gold badges27 silver badges60 bronze badges
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Not the answer you're looking for? Browse other questions tagged arraysexcelvbacountif or ask your own question.
Arguments are the values that functions use to perform calculations. In spreadsheet programs such as Excel and Google Sheets, functions are just built-in formulas that carry out set calculations and most of these functions require data to be entered, either by the user or another source, in order to return a result.
Function Syntax
A function's syntax refers to the layout of the function and includes the function's name, parenthesis, comma separators, and its arguments.
The arguments are always surrounded by parentheses and individual arguments are separated by commas.
A simple example, shown in the image above, is the SUM function, which can be used to sum or total long columns or rows of numbers. The syntax for this function is:
SUM (Number1, Number2, .. Number255)
The arguments for this function are: Number1, Number2, .. Number255
Number of Arguments
The number of arguments that a function requires varies with the function. The SUM function can have up to 255 arguments, but only one is required — the Number1 argument. The remainder are optional.
The OFFSET function, meanwhile, has three required arguments and two optional ones.
Other functions, such as the NOW and TODAY functions, have no arguments, but draw their data — the serial number or date — from the computer's system clock. Even though no arguments are required by these functions, the parentheses, which are part of the function's syntax, must still be included when entering the function.
Types of Data in Arguments
Like the number of arguments, the types of data that can be entered for an argument will vary depending upon the function.
In the case of the SUM function, as shown in the image above, the arguments must contain number data, but this data can be:
- the actual data being summed - the Number1 argument in the image above
- an individual cell reference to the location of the number data in the worksheet - the Number2 argument
- an array or range of cell references - the Number3 argument
Other types of data that can be used for arguments include:
- text data
- other functions
Nesting Functions
It is common for one function to be entered as the argument for another function. This operation is known as nesting functions and it is done to extend the capabilities of the program in carrying out complex calculations.
For example, it is not uncommon for IF functions to be nested one inside the other as shown below.
In this example, the second or nested IF function is used as the Value_if_true argument of the first IF function and is used to test for a second condition, if the data in cell A2 is less than 100.
Since Excel 2007, 64 levels of nesting are permitted in formulas. Prior to that, only seven levels of nesting were supported.
Finding a Function's Arguments
Two ways of finding the argument requirements for individual functions are:
- Open the function's dialog box in Excel
- Tooltip windows in Excel and Google Sheets
Excel Function Dialog Boxes
The vast majority of functions in Excel have a dialog box, as shown for the SUM function in the image above, that lists the required and optional arguments for the function.
Opening a function's dialog box can be done by:
- finding and clicking on a function's name under the Formulatab of the ribbon;
- clicking on theInsert Function option located next to the formula bar, as indicated in the image above.
Tooltips: Typing a Function's Name
Another way to find out a function's arguments in Excel and in Google Sheets is to:
- Click on a cell,
- Enter the equal sign - to notify the program that a formula is being entered
- Enter the function's name - as you type, the names of all functions starting with that letter appear in a tooltip below the active cell;
- Enter an open parenthesis - the specified function and its arguments are listed in the tooltip.
In Excel, the tooltip window surrounds optional arguments with square brackets ([ ]). All others listed arguments are required.
In Google Sheets, the tooltip window does not differentiate between required and optional arguments. Instead, it includes an example as well as a summary of the function's use and a description of each argument.
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
This video is part of a training course called Advanced IF functions.
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The COUNTIFS function syntax has the following arguments:
