How to Sum If Cell Contains Number in Excel (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


sum if cell contains number

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.

Dataset for the article


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),""))
Using a formula that combines SUM, IFERRROR, LEFT, FIND functions.

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)

Applying SUMPRODUCT and ISNUMBER functions to calculate the total sum of sales

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))

Using SUMPRODUCT and LEFT functions to sum if cells begin with specific texts.

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)

Substituting texts using SUBSTITUTE function and then calculating sum

πŸ” 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)

Using the SUBSTITUTE function to substitute multiple texts and calculate sum

πŸ” 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")

Using SUMIF function to calculate sum based on a criteria

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)

Using the SUMIF function to sum if a cell contains a specific text

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.

Using the SUMIFS function to calculate sum if cell contains text in another cell


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.


Download Practice Workbook

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md Sakibul Hasan Nahid
Md Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo