Table of Contents
The SUMIF function in Excel allows users to calculate the sum of values that meet certain criteria within a specified range. This function can also be used to calculate the sum of values that meet multiple criteria across different ranges. By specifying the criteria in separate ranges, the function can accurately sum up the values that satisfy all the specified conditions. This feature is particularly useful for complex data analysis and allows for efficient and accurate calculations of sums based on multiple criteria.
Excel: Use SUMIF with Multiple Ranges
You can use the following basic syntax to perform a SUMIF function with multiple ranges in Excel:
=SUMIF(A2:A11,G2,B2:B11) + SUMIF(A2:A11,G2,C2:C11)
This particular formula adds the values in the ranges B2:B11 and C2:C11 where the corresponding values in the range A2:A11 are equal to G2.
The following example shows how to use this syntax in practice.
Example: Using SUMIF with Multiple Ranges in Excel
Suppose we have the following dataset that shows the number of points scored by various basketball players during two different games:
Now suppose we would like to calculate the total sum of all points scored between both games for only the players on the Mavs team.
We can use the following formula to do so:
=SUMIF(A2:A11,E2,B2:B11) + SUMIF(A2:A11,E2,C2:C11)
We’ll type this formula into cell F2 and then press Enter:
We can see that the total sum of all points scored between both games for the players on the Mavs team was 140.
We can verify this is correct by manually taking the sum of all points values for the Mavs players between the two games:
Total Points: 22 + 20 + 15 + 32 + 12 + 14 + 10 + 15 = 140.
This matches the value that we calculated using our formula.
Note: In this example, we used the SUMIF() function with two cell ranges, but we can use similar syntax to include any number of cell ranges that we’d like.
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 calculate the sum of values that meet multiple criteria across different ranges?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-calculate-the-sum-of-values-that-meet-multiple-criteria-across-different-ranges/
stats writer. "How can I use the SUMIF function in Excel to calculate the sum of values that meet multiple criteria across different ranges?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-calculate-the-sum-of-values-that-meet-multiple-criteria-across-different-ranges/.
stats writer. "How can I use the SUMIF function in Excel to calculate the sum of values that meet multiple criteria across different ranges?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-calculate-the-sum-of-values-that-meet-multiple-criteria-across-different-ranges/.
stats writer (2024) 'How can I use the SUMIF function in Excel to calculate the sum of values that meet multiple criteria across different ranges?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-sumif-function-in-excel-to-calculate-the-sum-of-values-that-meet-multiple-criteria-across-different-ranges/.
[1] stats writer, "How can I use the SUMIF function in Excel to calculate the sum of values that meet multiple criteria across different ranges?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the SUMIF function in Excel to calculate the sum of values that meet multiple criteria across different ranges?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.