OFFSET Inside SUMIFS with Multiple Criteria [Single Cell Report]

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!

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *