Site icon ExcelNotes

How to Sum Cells Ending with a Number

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:

AmountStep 1Step 2Step 3
1,2788TRUE1
8000FALSE0
5888TRUE1
3000FALSE0
3000FALSE0
2000FALSE0
3000FALSE0
8000FALSE0
5000FALSE0

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.

Exit mobile version