| | | The "range" must be a cell range or a named range. |
| | | The "range" does not have to be sorted into any order. |
| | | The "criteria" can be a cell reference or a named range. |
| | | The "criteria" can be in the form of a number, expression, or text. |
| | | The "criteria" can be expressed as numerical (i.e. 32) or as a string (i.e. "32"). |
| | | The "criteria" can use string matching, i.e. *M* is all words that contain the letter "M". This is not case sensitive. |
| | | If you are checking for numerical conditions make sure the cells contain numbers and not text. |
| | | Example 1 - This counts the number of cells that contain the text "apples". |
| | | Example 2 - This counts the number of cells that contain a negative number. |
| | | Example 3 - This counts the number of cells that contain a number less than 40. |
| | | Example 4 - This counts the number of cells that contain a number greater than 40. |
| | | Example 5 - This counts the number of cells that contain non zero values. |
| | | Example 6 - This counts the number of cells that contain a value between 1 and a 100. |
| | | Example 7 - This counts the number of cells that contain text. |
| | | Example 8 - This counts the number of cells that contain the letter "B". This is not case sensitive. |
| | | Example 9 - This counts the number of cells that contain the letter "b". This is not case sensitive. |
| | | Example 10 - This counts the number of cells that contain only three letters. |
| | | Example 11 - This counts the number of cells that have the letter "e" as their second character. |
| | | Example 12 - This counts the number of cells that contain the text "Y". |
| | | Example 13 - This counts the number of cells that contain either the text "Y" or the text "N". |
| | | | A | B | C | | 1 | =COUNTIF(B1:C8,"apples") = 0 | 20 | 35 | | 2 | =COUNTIF(B1:C8,"<0") = 1 | -40 | 85 | | 3 | =COUNTIF(B1:C8,"<40") = 3 | 60 | 125 | | 4 | =COUNTIF(B1:C8,">40") = 7 | 180 | 95 | | 5 | =COUNTIF(B1:C8,"<>0") = 16 | 500 | 55 | | 6 | =COUNTIF(B1:C8,">=1")-COUNTIF(B1:C8,">=100") = 6 | Better | dot | | 7 | =COUNTIF(B1:C8,"*") = 6 | Solutions | com | | 8 | =COUNTIF(B1:C8,"*B*") = 1 | Y | N | | 9 | =COUNTIF(B1:C8,"*b*") = 1 | | | | 10 | =COUNTIF(B1:C8,"???") = 2 | | | | 11 | =COUNTIF(B1:C8,"?e*") = 1 | | | | 12 | =COUNTIF(B1:C8,"Y") = 1 | | | | 13 | =COUNTIF(B1:C8,"Y")+COUNTIF(B1:C8,"N") = 2 | | |
| |
