Table of Contents
The SUMIF function in Excel is a useful tool that allows users to add only the numbers in a specified range based on a given criteria. This function is particularly helpful when working with large sets of data, as it allows for quick and accurate calculations based on specific conditions. To use the SUMIF function, simply specify the range of numbers and the criteria to be met, and Excel will automatically add only the relevant numbers. This can save time and effort when performing calculations and analyzing data.
Excel: Use SUMIF with ISNUMBER
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:
Cite this article
stats writer (2024). How can I use the SUMIF function in Excel to only add numbers in a specified range based on a given criteria?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-only-add-numbers-in-a-specified-range-based-on-a-given-criteria/
stats writer. "How can I use the SUMIF function in Excel to only add numbers in a specified range based on a given criteria?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-only-add-numbers-in-a-specified-range-based-on-a-given-criteria/.
stats writer. "How can I use the SUMIF function in Excel to only add numbers in a specified range based on a given criteria?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-only-add-numbers-in-a-specified-range-based-on-a-given-criteria/.
stats writer (2024) 'How can I use the SUMIF function in Excel to only add numbers in a specified range based on a given criteria?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-only-add-numbers-in-a-specified-range-based-on-a-given-criteria/.
[1] stats writer, "How can I use the SUMIF function in Excel to only add numbers in a specified range based on a given criteria?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SUMIF function in Excel to only add numbers in a specified range based on a given criteria?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.