Problem:
How can we count the cells in List1 (column A) according to the following criteria:
1. Cell contains a number.
2. Cell is not empty.
3. Cell is empty.
4. Cell contains a number larger than 5.
5. Cell contains text.
6. Cell contains the string \"Excel\".
7. Cell contains 3 characters only.
Solution:
Use the COUNT, COUNTA, COUNTBLANK, and COUNTIF functions as shown in the following formulas:
1. =COUNT(A2:A13)
2. =COUNTA(A2:A13)
3. =COUNTBLANK(A2:A13)
4. =COUNTIF(A2:A13,\">5\")
5. =COUNTIF(A2:A13,\"*\")
6. =COUNTIF(A2:A13,\"*Excel*\")
7. =COUNTIF(A2:A13,\"???\")
To replace the criteria specified in the COUNTIF function with a cell reference containing the desired criteria, include the following operators:
The same method can be applied when using the \"*\" operator (wildcard) to count cells containing a particular substring.
Thus, with the information stored in C11:C14, the above COUNTIF functions (4-7) could be modified, as follows:
1. =COUNTIF(A2:A13,\">\"&C11)
2. =COUNTIF(A2:A13,C12)
3. =COUNTIF(A3:A13,\"*\"&C13&\"*\")
4. =COUNTIF(A2:A13,C14)
Bookmarks