How to sum a specified row or column in a named range in Excel?
Author: SiluviaLast Modified: 2020-09-04
It is easy to sum a certain row or column in a worksheet in Excel. However, do you know how to sum a specified row or column within a named range in Excel? This article will show you methods to achieve it.
Supposing you have specified the range B2:E5 as a named range “Score_range” as below screenshot shown. Please do as follows to sum the certain row (says the second row) in this named range.
1. Select a blank cell, enter formula = SUM(OFFSET(Score_range,1,0,1,COLUMNS(Score_range))) into the Formula Bar and then press the Enter key. See screenshot:
Notes:
1. In the formula, Score_range is the name of the named range you want to sum certain row inside.
2. The first number 1 represents the second row you will sum in the specified named range. If you want to sum the first row in the named range, please change this number to 0; and if you want to sum the third row, please change it to 2.
By now you have summed the second row in the named range “Score_range”.
Sum a specified column in a named range with formula
Just take the above screenshot as an example. Here I will show you the formula of summing the second column within this named range.
1. Select a blank cell and enter formula =SUM(INDEX(Score_range,,2)) into the Formula Bar and then press the Enter key to get the result. See screenshot:
Notes:
1. In the formula, Score_range is the name of the named range you want to sum certain column inside.
2. The number 2 represents the second column you will sum in the specified named range. If you want to sum the first row in the named range, please change this number to 1; and if you want to sum the third row, please change it to 3.
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
