Sum Series Rows Formula in Excel: Calculate Sum of Two Columns Without Creating Another

Calculating the Sum of Two Columns in Excel Without Creating Another

In Excel, it is often necessary to calculate the sum of two columns without creating another column. This can be done using the SUM function in combination with other functions such as INDEX, MATCH, and ROW. In this article, we will explore how to calculate the sum of two columns in Excel using these functions.

Using the SUM Function

The SUM function is used to add up a range of cells in Excel. To calculate the sum of two columns, we can use the SUM function in combination with the INDEX and MATCH functions. The INDEX function returns the value of a cell in a given range, while the MATCH function returns the relative position of a value in a given range.

For example, suppose we have the following table:

   A     B     C
1 Value Percent1 Percent2
2  100     50%     50%
3  200     40%     60%
4  300     30%     70%
5  400     20%     80%
6  500     10%     90%

To calculate the sum of columns B and C, we can use the following formula:

=SUM(INDEX(B:C,MATCH(ROW(B:B),ROW(B:B),0)):INDEX(B:C,MATCH(ROW(B:B),ROW(B:B),0)+COUNTIF(B:B,">0")-1))

This formula uses the INDEX function to return the range of cells in columns B and C that contain values. The MATCH function is used to find the first row that contains a value in column B. The COUNTIF function is used to count the number of rows in column B that contain a value. The formula then uses the SUM function to add up the values in the returned range.

Using the ROW Function

Another way to calculate the sum of two columns in Excel without creating another column is to use the ROW function. The ROW function returns the row number of a given cell. We can use this function to create an array of row numbers, and then use this array to calculate the sum of the two columns.

For example, suppose we have the following table:

   A     B     C
1 Value Percent1 Percent2
2  100     50%     50%
3  200     40%     60%
4  300     30%     70%
5  400     20%     80%
6  500     10%     90%

To calculate the sum of columns B and C, we can use the following formula:

=SUM(B2:INDEX(B:B,MATCH(1E+99,B:B))+C2:INDEX(C:C,MATCH(1E+99,C:C)))

This formula uses the INDEX function to return the last cell in columns B and C that contains a value. The MATCH function is used to find the last cell in columns B and C that contains a value. The formula then uses the SUM function to add up the values in the returned ranges.

• Calculating the sum of two columns in Excel without creating another column can be done using the SUM function in combination with other functions such as INDEX, MATCH, and ROW.
• The INDEX function returns the value of a cell in a given range, while the MATCH function returns the relative position of a value in a given range.
• The ROW function returns the row number of a given cell, and can be used to create an array of row numbers to calculate the sum of two columns.

References

• SUM function
• INDEX function
• MATCH function
• ROW function