Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use of the most rarely known and volatile Lookup functions called OFFSET combined with SUMIFS function. Let’s get started
OFFSET Function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. Users have the capability to specify number of rows and the numbers of columns to be returned.
Note, the rows argument can be positive (below the starting reference) or negative (above the starting reference). In addition, the cols argument can be positive (right of the starting reference) or negative (left of the starting reference). In this example, the cols argument is negative)
In order to make the formula easily understandable and readable, I applied named ranges to the data
Syntax =OFFSET(reference, rows, columns, [height], [width])
For this example, we want to calculate the Total generated by each Account Manager for Year 2013: to 2017. The caption below is the data we will work with
- In cell B1045, execute the formula:
=SUMIFS(OFFSET($O$3,1,-2,ROWS(Total),1),Account_Manager,$A1045,Year,B$1044)
In the formula above, we deployed the SUMIFS function and wrapped OFFSET function inside it
$O$3 is the absolute reference of the OFFSET reference argument
1 is the number of rows to offset below the starting reference
-2 is the number of columns to offset left of the starting reference
ROWS(Total) is the Total named range which returns the number of row height
1 is the column width
Account_Manager is the named range of the SUMIFS criteria_range1
$A1045 is the absolute and relative cell references that governs the SUMIFS criteria1
Year is the named range of the SUMIFS criteria_range2
B$1044 is the relative and absolute cell references that governs the SUMIFS criteria2
- Click ENTER
- Copy the formula across and down using the auto-fill
In the caption below, the Total for Abiola David for 2016 is ₦49,857.46
To verify, we applied multiple filter to Year and Account Manager displaying 2016 and Abiola David. From the Status Bar of the caption below, we can see that all the selection correlates with the value in cell E1045.
