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

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

Using a formula that combines SUM, IFERRROR, LEFT, FIND functions.

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.

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.

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.

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.

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

Finding Sum If Cells Contain Numbers.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!