Sometimes, we need to sum if a cell contains a number in Excel. We need to do this very often in data analysis and financial modeling. Sometimes the range can contain different data types or we may need to sum based on some specific criteria. In this article, we are going to learn how to sum if a cell contains a number in Excel.

**Watch Video – Sum If Cell Contains Number in Excel**

Here we can see that the price of the Google Pixel 6 is in text format. Using the formula we have calculated the total price including the price of the Google Pixel 6. Although the cell contains a number in text format.

**Table of Contents**hide

## Sum If a Cell Contains Number in Excel: 6 Examples

In the dataset, we can see some products, and their prices are given. Some prices are in number format and some are in text. Here we will try to get the total price sum if the cell contains any kind of number.

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

In this example, we are going to combine some functions to sum if a cell contains numbers. Here the price of the Google Pixel 6 contains a number. But because of the text after the number the cell is in text form. Now if we use the **SUM **function it will not consider the 900. To do this we will use a combination of **SUM**, **IFERROR**, **LEFT**, and **FIND** functions.

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

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

This will calculate the price of Google Pixel 6 which is in text form. Then calculate the total sum.

**🔍 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

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

In this example, we will calculate the sum of sales of those employees whose employee ids are in number form. Here we can see there are 3 employee ids in number form. So we want to calculate their sales using **SUMPRODUCT** and **ISNUMBER** functions. To do this we will follow the steps below.

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

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

This will return us the sum of the sales of those 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

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

Now we want to sum the sales of those employees whose employee id starts with “16”. For this, we will use a combination of **SUMPRODUCT** and **LEFT** functions and will follow the steps given below.

- 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

### 4. Calculate Sum If Cells Contain Text and Numbers Together in Excel

In the dataset below we can see that the **Price **column contains both text and number. The general **SUM **function will not consider text values while calculating. So we will use a combination of **SUM **and **SUBSTITUTE** functions to calculate the sum. To calculate this, select cell **C12 **and enter the formula given below.

`=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

### 5. Determine Sum If Cells Contain Different Text and Numbers in Excel

In this example, the dataset contains more than one kind of text in the **Price **column. One contains “(out of stock)” and another is “(Not Available)”. So for different text and numbers, we will select cell **C12** and 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.

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

Now we will calculate the sum based on specific criteria using the **SUMIF** function. Here in this dataset, we will calculate the sum of the prices which are greater than 700. To do this we will first select cell **C12** and enter the formula given below.

`=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

Now we will calculate the sum of values against some specific text. For example, here we will calculate the sales of the employees whose employee ids start with **“S”**. For this we will select cell **C12 **and will enter the formula given below.

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

This will calculate the sum of total sales for the employees whose employee ids start with “S”.

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

In this case, the dataset stores the** Product ID** and the **Price** of some products. The product id begins with the year the product was sold. We need to compute sales by year now. The first product, for example, was sold in 2016. So we’ll calculate total sales per year now. To do so, select cell **F5** and input the formula shown below.

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

Then drag the fill handle icon from** F5** to **F8**.

## Takeaways from This Article

- In this article, we used different functions and learned their uses.
- We have learned to convert a cell containing a number in text form into a number.
- We learned the use of the
**SUMIF**function and how we can use it to sum based on some specific conditions. - We have also used the
**SUMPRODUCT**and a few other functions.

## Frequently Asked Questions

**1. 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.

**2. 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.

**3. 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.

You can download the practice workbook from here.

## Conclusion

In this article, we learned how to sum if a cell contains numbers in Excel. We may need to sum based on different criteria or the range may contain text. In that case, we can ignore them or convert texts into numbers and then sum them using the described examples. If you have any questions or suggestions, you can ask in the comment section below.