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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Use SUMIF with OR in Excel
How to Use SUMIFS with a Date Range in Excel
How to Use Wildcard in SUMIFS Function in Excel
