How to Count Cells Ending with a Digit

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: To count the number of cells that are ending with 5

=SUMPRODUCT(--(RIGHT(C2:C12,1)="5"))

  • (--(RIGHT(C2:C12,1)="5")): Check the last 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 ending with "5".

Formula 2: To count the number of cells that are ending with 5

=SUMPRODUCT(--(MOD(C2:C12,10)=5))
Please change 5 to another number if the ending digit is different (e.g., 3).
This formula will work only for integers

  • (MOD(C2:C12,10)=5): The MOD function returns the remainder when a number is divided by another number. If the remainder is equal to 5, that meets the requirement and the result returns true, otherwise, it returns false;
  • 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 ending 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.

Leave a Reply