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.
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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
