To count the numbers ending with one or more digits, you can use the RIGHT function. The RIGHT function is a text function but also works for numbers.

**Example:** You are working with a worksheet with the product in column A, the amount in column B, and the profit/loss in column C.

**Question: What is the sum of numbers ending with 8 in column B?**

=SUMPRODUCT(--(RIGHT(B2:B10,1)="8"), B2:B10)

The result returns 1,866.

**Explanation:**

- Step 1: RIGHT(B2:B10,1): To have the last digit of each number;
- Step 2: (RIGHT(B2:B10,1)="8"): If the last digit equals 8, it returns TRUE; otherwise, it returns FALSE;
- Step 3: (--(RIGHT(B2:B10,1)="8")): The double hyphen converts TRUE into one and FALSE into zero;
- Step 4: The SUMPRODUCT function returns the sum of the products of the new array.

Amount | Step 1 | Step 2 | Step 3 |
---|---|---|---|

1,278 | 8 | TRUE | 1 |

800 | 0 | FALSE | 0 |

588 | 8 | TRUE | 1 |

300 | 0 | FALSE | 0 |

300 | 0 | FALSE | 0 |

200 | 0 | FALSE | 0 |

300 | 0 | FALSE | 0 |

800 | 0 | FALSE | 0 |

500 | 0 | FALSE | 0 |

You can use the following formula to have the sum of the numbers in column C when the corresponding cells in column B end with 8.

=SUMPRODUCT(--(RIGHT(B2:B10,1)="8"),C2:C10)

The result returns 1,358.77.

**Notes: The SUMPRODUCT Function**

The SUMPRODUCT function adds all the multiplication results for all arrays.

**Formula:**

=SUMPRODUCT(array1, [array2], …)

**Explanations:**

– Array1 is required; the first array is to multiply and add.

– Array2 is optional; the second array is to multiply and add.