But notice that when matching to text, the text MUST be enclosed in quotation marks. But sometimes, we need to use multiple criteria for counting, this will be more complex, today, I will talk about some items for counting with multiple criteria. Using the worksheet below, the function =COUNTIF(B1:B6,"Yes") would result in a value of 3. In Excel, COUNTIF function may help us to calculate the number of a certain value in a list. In the spreadsheet below, cells A1-A6 and B1-B6 contain data, and cells C1-C6 contain the actual formulas that we show in column D.Ībove we showed an exact match using a number. 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. Worksheet Examples of the COUNTIF Function If an * or ? is used as text in criteria and not as a wildcard, the symbol must be preceded by a tilde (~). The COUNTIFS Function in Excel returns a value corresponding to the number of cells in the user-defined scope(s) or range(s) that match one or more defined criteria. "S?") matches 5-digit values beginning with the letter S). The question mark represents one character (e.g. "S*") matches any value beginning with the letter S). The text may contain numbers, but the wildcards do not work with cells that contain only numbers.Īn asterisk matches any sequence of characters (e.g. The wildcard characters asterisk (*) and question mark (?) may be used for text values only.
Here is a simple example of the COUNTIF function: The SUMIFS function adds only the values that meet multiple criteria. The SUMIF function adds only the values that meet a single criteria. To count cells using a single criteria, use the COUNTIF function. The use of other operators is covered in our COUNTIF Advanced tutorial.Ĭriteria that contains text must be wrapped in quotation marks, numbers do not. To count cells that arent blank, use the COUNTA function. When other operators are used, special rules must be followed.
(Note: This method requires you have filtered your original table before following steps.)1.Find a blank cell besides the original filtered table, says Cell G2, enter IF(B2'Pear',1,''), and then drag the Fill Handle to the range you need. The default action for criteria is "equals" and cannot be specified. This method will guide you to add an extra help column, and then you can apply the COUNTIFS function to count the filtered data in Excel. ► criteria is the value a cell must have to be counted.