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
Hello,
I’m using the following formula, =IF(ISNUMBER(SEARCH(“WNYD UPS”,$A57)),”1.2″,””) This gives me a 1.2 in the cell. I’m trying to add up the row using a =sum(D56:CA56) but it just shows up as a 0. Any ideas how I can get it to add up? The rest of the cells in the row have a similar formula but to not give a value because it’s not met.
Thanks,
Jack
Hi Jack…The issue is that your formula is returning “1.2” as text because you have it inside quotation marks. SUM ignores text values, so it treats those cells like they are zero.
Try changing the formula to:
`=IF(ISNUMBER(SEARCH(“WNYD UPS”,$A57)),1.2,””)`
Notice that the `1.2` no longer has quotation marks around it. That will make Excel treat it as a number, and then your SUM formula should work normally.
Also, just double-check your row numbers. Your example formula references row 57, but your SUM formula is:
`=SUM(D56:CA56)`
If the values you want to add are actually on row 57, you would want:
`=SUM(D57:CA57)`
So the two things I would check are removing the quotes around 1.2 and making sure the SUM is pointing to the correct row.