COUNTIF(range, criteria)

Returns the number of non blank cells that satisfies one condition.

rangeThe range of cells from which you want to count the cells.
criteriaThe logical test that will filter out the data.

Remarks

* This function includes hidden rows.
* This function uses the actual cell values and not the displayed formatted values.
* If you are checking for numerical conditions make sure the cells contain numbers and not text.
* 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.
* The "criteria" can use string matching, i.e. ?M is all words that have the letter "M" as there second character.
* You can use the COUNT function to return the number of non blank cells with no conditions.
* You can use the COUNTA function to include logical values and text.
* You can use the COUNTIFS function to satisfy multiple conditions.
* This is similar to the AVERAGEIF function.
* This is similar to the SUMIF function.
* For an illustrated example refer to the page under Advanced Functions.
* For the Microsoft documentation refer to support.office.com

 ABC
1=COUNTIF(B1:C8,"apples") = 02035
2=COUNTIF(B1:C8,"<0") = 1-4085
3=COUNTIF(B1:C8,"<40") = 360125
4=COUNTIF(B1:C8,">40") = 718095
5=COUNTIF(B1:C8,"<>0") = 1650055
6=COUNTIF(B1:C8,">=1")-COUNTIF(B1:C8,">=100") = 6Betterdot
7=COUNTIF(B1:C8,"*") = 6Solutionscom
8=COUNTIF(B1:C8,"*B*") = 1YN
9=COUNTIF(B1:C8,"*b*") = 112-Jul-200821-Jul-2020
10=COUNTIF(B1:C8,"???") = 2  
11=COUNTIF(B1:C8,"?e*") = 1  
12=COUNTIF(B1:C8,"Y") = 1  
13=COUNTIF(B9:C9,"<"&TODAY()) = 1  
14=COUNTIF(B9:C9,"<"&TODAY()-30) = 1  
15=COUNTIF(B1:C8,"Y")+COUNTIF(B1:C8,"N") = 2  

1 - This counts the number of cells that contain the text "apples".
2 - This counts the number of cells that contain a negative number.
3 - This counts the number of cells that contain a number less than 40.
4 - This counts the number of cells that contain a number greater than 40.
5 - This counts the number of cells that contain non zero values.
6 - This counts the number of cells that contain a value between 1 and 100.
7 - This counts the number of cells that contain text.
8 - This counts the number of cells that contain the letter "B". This is not case sensitive.
9 - This counts the number of cells that contain the letter "b". This is not case sensitive.
10 - This counts the number of cells that contain only three letters.
11 - This counts the number of cells that have the letter "e" as their second character.
12 - This counts the number of cells that contain the text "Y".
13 - This counts the number of cells that contain a date that is less than todays date.
14 - This counts the number of cells that contain a date that is less than 30 days before todays date.
15 - This counts the number of cells that contain either the text "Y" or the text "N".

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top