Welcome to Excel Jet Consult blog post. In this Microsoft Excel tutorial, we will learn how to use the OFFSET function inside SUMIFS to perform multiple criteria calculation inside a single cell.
Video
Sample data
In the screenshot below, we have a Sales Data that is stored as an Excel Tables.
Calculation
In cell J2, we want to write a shingle formula that will deliver the Total Sales for Brand that equals to BMW, Ford and Limousine. In addition, we want Top 5 Total Sales all in cell J2. Let’s dive in
In cell J2, execute the formula:
=SUM(SUMIFS(OFFSET(Sales[[#Headers],[Brand]],1,4,ROWS(Sales[Sales Amount]),1),Sales[Brand],J1:L1,Sales[Sales Amount],”>=”&LARGE(Sales[Sales Amount],5)))
Click Enter
Based on the above formula, we started off by:
Using SUMIFS Function. Then, we nested OFFSET Function as the sum range
Next, we provided the reference argument for the OFFSET which is the structured reference syntax: Sales[[#Headers],[Brand]]
Next, we typed in 1 as rows input value which means we moved one row down from the reference
Next, we typed in 4 as the cols input value which implies we moved four columns to the right of the reference
Next, we used ROWS(Sales[Sales Amount]) as the height argument which returned the number of rows in the dataset.
We just 1 as the width argument input value and close the OFFSET formula.
After, the equal sign of the SUMIFS Function, we provided Sales[Brand] and J1:L1 as the criteria range1 and criteria1 respectively.
Next, we provided Sales[Sales Amount],”>=”&LARGE(Sales[Sales Amount],5) as the criteria range2 and criteria2 respectively. This gave us the Top 5
Finally, we wrapped the entire formula inside the SUM aggregate function to deliver a single cell report
See the screenshot below
See you in the next blog post!