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 ending with a digit, and please check here to count cells starting 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 1: How many cells end with 5?
=SUMPRODUCT(–(RIGHT(C2:C12,1)="5"))
=SUMPRODUCT((RIGHT(C2:C12,1)="5")*1)
The result returns 3, so three numbers in column C end with "5".
- Step 1: (–(RIGHT(C2:C12,1)="5")): Check the last digit and see if the result is equal to 5;
- Step 2: The double hyphen (or times 1) is to convert true into one and false into zero;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array.
Alternatively, you can combine the SUMPRODUCT and MOD functions if your data are numeric values.
Formula 2: How many cells end with 5?
=SUMPRODUCT(–(MOD(C2:C12,10)=5))
=SUMPRODUCT((MOD(C2:C12,10)=5)*1)
- Step 1: (MOD(C2:C12,10)=5): The MOD function returns the remainder when a number is divided by another. If the remainder is equal to 5, that meets the requirement, and the result returns true; otherwise, it returns false;
- Step 2: The double hyphen (or times one ) is to convert true into one and false into zero;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array.
The result returns 3, so three numbers in column C end with "5".
Notes: The MOD Function
Formula:
=MOD(number, divisor)
Explanations:
– The number is required, the number to find the reminder.
– The divisor is required, which is the divider.