Pinterest Pixel

Sum the Last 7 Transactions with the Offset Function

John Michaloudis
In this tutorial, you will learn how to show Excel Sum Last 3 Entries in a Row using the OFFSET function.
The OFFSET function in Excel is one of the Lookup functions and is great if you want to reference a range of cells and use that reference to do a calculation.

Let's understand the basics of an OFFSET Function by going through the Formula Breakdown below.

In this tutorial, you will learn how to show Excel Sum Last 3 Entries in a Row using the OFFSET function. The OFFSET function in Excel is one of the Lookup functions and is great if you want to reference a range of cells and use that reference to do a calculation. Let’s understand the basics of an OFFSET Function by going through the Formula Breakdown below.

What does it do?

It returns a reference to a range, from a starting point to a specified number of rows, columns, height, and width of cells

Formula breakdown:

=OFFSET(reference, rows, columns, [height], [width])

What it means:

=OFFSET(start in this cell, go up/down a number of rows, go left/right a number of columns, height of range, width of range)


Click here to see how the formula works visually & interactively 

 

Now that you are familiar with the OFFSET function, let’s move ahead and understand how to show Excel Sum Last 7 Entries in a Row.

It is often used when you need to reference a range that is moving or resizing. For example, if we have daily sales going down one row per day, then we can Sum the last X transactions.

Watch Excel sum last n values in a row on YouTube and give it a thumbs-up!

Sum the Last 7 Transactions with the Offset Function | MyExcelOnline

Follow the step-by-step tutorial on How to show Excel sum last n rows  and download this Excel workbook to practice along:

Download excel workbookOffset-Last-7-Transactions.xlsx

 

STEP 1: We need to enter the Sum function as we are going to Sum the last 7 transactions

(We can also add the Average function if we want to show the Average of the last 7 transactions):

Table of Contents

=SUM

 

STEP 2: We need to enter the Offset function so we can reference the range that we want to Sum:

=SUM(OFFSET

 

STEP 3: 1st Offset argument – Where do we want to to start our reference?

This is at the start of the Daily Sales list:

=SUM(OFFSET(D11

Sum the Last 7 Transactions with the Offset Function

 

STEP 4: 2nd Offset argument – How many Rows down do we want to go?

This is where the COUNTA function is used to count how many transactions we have in total and go down to the last cell in our list.

So as we are starting at cell D11, we are going to go down 11 cells ( COUNTA(D11:D100) ) and end up after the last cell with a value.

NB: It is always a good idea to enter an ending range that is more than your last data cell.  That way as your new data get entered, we will be sure to capture it!

=SUM(OFFSET(D11, COUNTA(D11:D100)

Sum the Last 7 Transactions with the Offset Function

 

STEP 5: How many of the last sales transactions do we want to Sum?  7

So we need to take away 7 from the COUNTA formula which means that our Offset function will go up by 7 cells:

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,

Sum the Last 7 Transactions with the Offset Function

 

STEP 6: 3rd Offset argument – How many Columns to the right/left do we want to move?

We do not want to move to any Columns, so we simply enter 0

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,

 

STEP 7: 4th Offset argument – How High do we want our referenced data to be?

Since we want to Sum 7 transactions, then we need the [height] to be 7 cells high:

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,7,

Sum the Last 7 Transactions with the Offset Function

STEP 8: 5th Offset argument – How Wide do we want our referenced data to be?

We want to reference 1 Column only, so we need to enter 1 for the [width]

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,7,1))

Sum the Last 7 Transactions with the Offset Function

As we can see the Sum of the last 7 transactions from our example is $28,472.

Sum the Last 7 Transactions with the Offset Function

If we add more transactions at the bottom of our Sales list then it automatically returns us the last 7 transactions, without the need to update the formula 🙂

Sum the Last 7 Transactions with the Offset Function

This completes our tutorial on Excel sum last 7 values in row!

Further Learning:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  DATE Formula in Excel

Steps To Follow

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...