You can use the LEFT function to have the sum of the numbers starting with one or more digits. The LEFT 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 the numbers starting with 8 in column B?
=SUMPRODUCT(–(LEFT(B2:B10,1)="8"),B2:B10)
The result returns 1,600.
Explanation:
- Step 1: LEFT(B2:B10,1): To have the first digit of each number;
- Step 2: (LEFT(B2:B10,1)="8"): If the first digit equals 8, it returns TRUE; otherwise, it returns FALSE;
- Step 3: (–(LEFT(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 | 1 | FALSE | 0 |
| 800 | 8 | TRUE | 1 |
| 588 | 5 | FALSE | 0 |
| 300 | 3 | FALSE | 0 |
| 300 | 3 | FALSE | 0 |
| 200 | 2 | FALSE | 0 |
| 300 | 3 | FALSE | 0 |
| 800 | 8 | TRUE | 1 |
| 500 | 5 | 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 start with 8.
=SUMPRODUCT(–(LEFT(B2:B10,1)="8"),C2:C10)
The result returns -777.
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.