3 ways to calculate the sum by range using the SUM function in Excel
3 ways to calculate the sum by range using the SUM function in Excel: The SUM function is a basic function that can be applied to calculate the sum in many different cases. In this article, Buffcom.net will share with you how to calculate the sum of multiple ranges, the sum of intersecting ranges, and the sum of contiguous ranges using the SUM function.
1. The structure of the SUM function
Syntax: =SUM(number1,[number2],…)
Where:
- number1: a required argument, which is the first number you want to add. This number can be 4, a cell reference like B6, or a range like B2:B8.
- number2-255: optional arguments, which are the second to 255th numbers you want to add. These numbers can be 4, a cell reference like B6, or a range like B2:B8.
For example, to calculate the sum from cell F3 to F7, use the following formula:
=SUM(F3:F7)
3 ways to calculate the sum by range using the SUM function in Excel
2. Use the SUM function to calculate the sum of multiple ranges
You can use the SUM function to calculate the sum of multiple ranges at the same time. For example, to calculate the sum from cell B3 to B7, cell D3 to D7, and cell F3 to F7, use the following formula:
=SUM(B3:B7;D3:D7;F3:F7)
3 ways to calculate the sum by range using the SUM function in Excel
3. Use the SUM function to calculate the sum of intersecting ranges
For example, to calculate the sum of the intersecting range between two ranges B5:F5 and D3:E7, use the following formula:
=SUM(B5:F5 D3:E7)
Note that the short way between two ranges is a space. The result will be the intersecting range between two ranges, which is cell D5 and E5 with a total value of 13 + 14 = 27.
3 ways to calculate the sum by range using the SUM function in Excel
4. Use the SUM function to calculate the sum of contiguous ranges
Similar to calculating the sum of one large range, you can use the colon operator (:) to separate the first cell from the last cell when referencing a contiguous range in a formula. For example, to calculate the sum from cell B3 to F7, besides entering =SUM(B3:F7), you can enter in other ways. For example:
=SUM(B3:C3:E7:F7)
=SUM(B3:B4:F6:F7)
=SUM(B6:B7:F3:F4)
3 ways to calculate the sum by range using the SUM function in Excel
Therefore, this article has shared with you 3 ways to calculate the sum by range using the SUM function in Excel. We hope this article will be helpful to you in your work. Good luck!
