Table of Contents
The SUMIF function in Excel can be used together with the ISNUMBER function to calculate the sum of cells that contain numerical values. This is done by using the ISNUMBER function to test each cell in a specified range and return TRUE if a value is numeric and FALSE if not, and then using the SUMIF function to add the values that returned TRUE. This is a useful function if you want to add all of the numerical values from a range without having to manually remove non-numeric values.
Often you may want to sum the values in a range in Excel only if the value in a corresponding range is a number.
You can use the following formula to do so:
=SUMPRODUCT(--ISNUMBER(A:A),B:B)
This particular formula sums the values in column B only where the corresponding value in column A is a number.
The following example shows how to use this formula in practice.
Example: Use SUMIF with ISNUMBER in Excel
Suppose we have the following dataset in Excel that shows the number of sales made by employees at a company with certain ID’s:
Notice that some of the ID values are characters while others are numerical values.
We can use the following formula to calculate the sum of sales only for the rows where the ID value is numeric:
=SUMPRODUCT(--ISNUMBER(A2:A11),B2:B11)
The following screenshot shows how to use this formula:
The sum of sales for only the rows where the ID is numeric is 67.
We can manually verify that this is correct by calculating the sum of sales for only the rows where the ID is numeric:
Sum of sales for numeric ID’s: 28 + 9 + 12 + 18 = 67.
This matches the value that we calculated using the formula.