COUNTIF function Office Support. 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 COUNTIFWhere do you want to look, What do you want to look forFor example COUNTIFA2 A5,LondonCOUNTIFA2 A5,A4Learn about using SUMIF, COUNTIF, and related functions for quick data analysis. Build your knowledge of SUMIF, COUNTIF, and related functions with Linked. In Learning. Learn how to use the COUNTIF function for quick data analysis. Watch free preview COUNTIFrange, criteriaArgument name. Descriptionrange    requiredThe 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. A number, expression, cell reference, or text string that determines which cells will be counted. For example, you can use a number like 3. 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. Dataapples. The site provides an introduction to understand the basics of and working with the Excel for performing basic statistical computation and its output managerial. The Excel community where you learn more about how to do something with Excel, discuss your work, and connect with the experts that build and use. MjntY0Y.png' alt='Microsoft Excel Count Multiple Criteria' title='Microsoft Excel Count Multiple Criteria' />In Excel, we can easily filter data based on one criterion with Filter function, but, a lot of times, we need to filter the data by multiple criteria in your large. Formula. DescriptionCOUNTIFA2 A5,applesCounts the number of cells with apples in cells A2 through A5. The result is 2. COUNTIFA2 A5,A4Counts the number of cells with peaches the value in A4 in cells A2 through A5. The result is 1. COUNTIFA2 A5,A2COUNTIFA2 A5,A3Counts 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. COUNTIFB2 B5, 5. Counts the number of cells with a value greater than 5. Pivot Table Data Range Update Windows. B2 through B5. The result is 2. COUNTIFB2 B5,lt B4Counts the number of cells with a value not equal to 7. B2 through B5. The ampersand merges the comparison operator for not equal to lt and the value in B4 to read COUNTIFB2 B5,lt 7. The result is 3. COUNTIFB2 B5, 3. COUNTIFB2 B5, 8. Counts the number of cells with a value greater than or equal to 3. B2 through B5. The result is 3. COUNTIFA2 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. COUNTIFA2 A5, 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 2. To match strings longer than 2. CONCATENATE function or the concatenate operator amp. For example, COUNTIFA2 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 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 arent 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, COUNTIFA2 A5,apple will count all instances of apple with a last letter that could vary. Jekyll And Hyde Lesson Activities For Reading more. Make sure your data doesnt contain erroneous characters. When counting text values, make sure the data doesnt 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 COUNTIFfruit, 3. COUNTIFfruit, 8. 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. To count cells that arent blank, use the COUNTA function. To count cells using multiple criteria, use the COUNTIFS function. See a video on how to use the COUNTIFS function. The SUMIF function adds only the values that meet a single criteria. The SUMIFS function adds only the values that meet multiple criteria. IFS function Office 3. Excel 2. 01. 6 and laterUse the TRIM function to remove leading and trailing spaces from cells. Use the CLEAN function to remove non printing characters from cells. Overview of formulas in Excel. How to avoid broken formulas. Detect errors in formulas. Statistical functions. Excel functions alphabeticalExcel functions by CategoryUsing SUMIF, COUNTIF, and related functions for quick data analysis free preview.