When you work with a worksheet with many different numbers, you may need to count the number of cells that meet a certain criterion. In this article, we will figure out the numbers starting with a digit, and please check here to count cells ending with a digit.
Example: You are working with a dataset with the product in column A, the date a product is sold in column B, and the sale amount in column C.
Formula: To count the number of cells that start with 5
- (--(LEFT(C2:C12,1)="5")): Check the first digit and see if the result is equal to 5
- The double hyphen (--) is to convert true into 1, and false into 0;
- The SUMPRODUCT function returns the sum of the products of the new array.
The result returns 3, and there are three numbers in column C that are starting with "5".
Notes: The LEFT Function
- Text is required. The text string that contains the characters you want to extract.
- Num_chars is optional. Specifies the number of characters you want LEFT to extract.