Here we can see that the price of the Google Pixel 6 is in text format. We have calculated the total price including the price of the Google Pixel 6.

## Sum If a Range Contains Numbers in Excel: 6 Examples

We can see some products and their prices. Some prices are in text, but we’ll try to get the sum of all available prices.

### Example 1 – Apply Combined Functions to Sum If a Cell Contains Numbers

- Select cell
**C12**and enter the given formula.

`=SUM(IFERROR(--LEFT(C5:C11,FIND(" ",C5:C11&" ")-1),""))`

This will count the price of Google Pixel 6 which is in text form.

** Formula Breakdown**

**FIND(” “,C5:C11&” “):**Here the**FIND**function will look for the first space in the range C5 to C11 and will return the position of the space.**LEFT(C5:C11,FIND(” “,C5:C11&” “)-1):**This part will extract the characters from a text to a given number. This will extract the characters until the first space. So it will return {900,1200,900,700,600,599,Not}.**IFERROR(–LEFT(C5:C11, FIND(” “, C5:C11&” “)-1),””):**The**IFERROR**function will look for error and if it finds any it will return an empty string. So it will return {900,1200,900,700,600,599}.**SUM(IFERROR(–LEFT(C5:C11,FIND(” “,C5:C11&” “)-1),””)):**Then, the**SUM**function will calculate the sum of the given values.

**Read More: **How to Sum If Cell Contains Number and Text in Excel

### Example 2 – Use SUMPRODUCT with ISNUMBER to Sum If Cells Contain Numbers in Excel

We will calculate the sum of sales of those employees whose employee IDs are in number form.

- Select cell
**C12**and enter the given formula.

`=SUMPRODUCT(--ISNUMBER(B5:B11),C5:C11)`

This will return us the sum of the sales of three employees.

** Formula Breakdown**

**–ISNUMBER(B5:B11):**This will return 1/0 if it finds any number in the B5:B11 range this will return 1 otherwise 0. So we will get the output as {0,1,0,0,1,0,1}.**SUMPRODUCT(–ISNUMBER(B5:B11),C5:C11):**This will multiply the first array and second array and will return the sum. Here the first array is {0,1,0,0,1,0,1} and it will multiply with the sales. Then, it will return the sum of the sales (C5:C11) of the employees with numeric employee ids.

**Read More: **How to Use Excel SUMIF with Greater Than Criterion

### Example 3 – Sum If Cells Begin with a Specific Number in Excel

We want to sum the sales of those employees whose employee id starts with “16”.

- Select cell
**C12**and enter the formula given below.

`=SUMPRODUCT(--(LEFT(B5:B11,2)+0=16)*(C5:C11))`

This will sum up all the sales of the employees whose IDs start with 16.

**Formula Breakdown**

**–(LEFT(B5:B11,2)+0=16):**This will check if the first two letters of the Employee ID match 16 or not. If it does it will return 1 otherwise 0. So here we will get an array of {1,1,1,0,1,0,1}.- This array will be multiplied by the sales value. And we will get the total sales of those employees.

**Read More: **How to Use Excel SUMIF to Sum Values Greater Than 0

### Example 4 – Calculate a Sum of Cells that Contain Text and Numbers Together in Excel

The following formula will remove the string ‘(out of stock)’ from cells and convert them in numbers (if possible) to be summed.

`=SUM(SUBSTITUTE(C5:C11, "(out of stock)", "")+0)`

** Formula Breakdown**

**SUBSTITUTE(C5:C11, “(out of stock)”, “”):**Here the**SUBSTITUTE**function will look for (out of stock) in the**C5:C11**range and will substitute it with an empty string. And we will get {“900”,”1200”,”900”,”700”,”600”,”599”,”1200”}. All values will convert into text form.- Then we will add 0 with this array and this will convert these text values into numbers.
- Then we will calculate the sum using the
**SUM**function.

**Read More: **How to Use SUMIF to SUM Less Than 0 in Excel

### Example 5 – Determine a Sum If Cells Contain Different Text and Numbers in Excel

- We’ll remove “(out of stock)” and “(Not Available)” from cells before summing them. Apply the formula given below.

`=SUM(SUBSTITUTE(SUBSTITUTE(C5:C11, "(out of stock)", ""),"(Not Available)", "")+0)`

**Formula Breakdown**

- Here the
**SUBSTITUTE**function replaces “out of stock” and “Not Available” with an empty string. - Then the remaining procedures are the same as the previous example.

### Example 6 – Calculate a Sum If a Cell Contains a Number Greater Than a Specific Value

- This formula calculates the sum of the prices greater than 700:

`=SUMIF(C5:C11,">700")`

This will calculate the sum of the values which are greater than 700.

## How to Sum If Cell Contains a Specific Text in Excel

We will calculate the sales of the employees whose employee IDs start with **“S”**.

- Select cell
**C12**and will enter the formula given below.

`=SUMIF(B5:B11,"S*",C5:C11)`

## How to Sum If a Cell Contains Text Matching Another Cell in Excel

The dataset stores the** Product ID** and the **Price** of some products. The product ID begins with the year the product was sold. We’ll calculate total sales per year.

- Enter the year values in E5 to E7.
- Select cell
**F5**and input the formula shown below.

`=SUMIFS($C$5:$C$13,$B$5:$B$13,"*"&E5&"*")`

- Drag the fill handle icon from
**F5**to**F7**.

## Frequently Asked Questions

**How do I sum cells with values in Excel?**

In Excel, you can use the **SUM** function to add values to cells. To sum the numbers in cells B1 through B5, for example, type “**=SUM(B1:B5)**” in the formula bar and hit Enter. The chosen cell will display the total of the values in that cell.

**How do I sum only numbers in Excel and ignore text in a cell?**

To sum only numbers in Excel and ignore text in a cell, you can use the Excel **SUM** function. The SUM function only takes the numeric values and ignores text.

**What is SUMPRODUCT in Excel?**

**SUMPRODUCT** is an Excel function that multiplies related array components and returns the total of the resultant products. It is capable of doing computations using various criteria and circumstances.

**Download the Practice Workbook**

## Related Articles

- Sum If Greater Than and Less Than Cell Value in Excel
- How to Use 3D SUMIF for Multiple Worksheets in Excel
- How to Use Excel SUMIF Function Based on Cell Color

**<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel**