{"id":2715,"date":"2021-04-01T14:22:15","date_gmt":"2021-04-01T12:22:15","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2715"},"modified":"2024-05-15T19:46:48","modified_gmt":"2024-05-15T17:46:48","slug":"sum-last-7-transactions-offset-function-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sum-last-7-transactions-offset-function-excel\/","title":{"rendered":"Sum the Last 7 Transactions with the Offset Function"},"content":{"rendered":"<p>In this tutorial, you will learn how to show Excel Sum Last 3 Entries in a Row using <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/lookup\/offset\/\" target=\"_blank\" rel=\"noopener\">the OFFSET<\/a> function.<\/p>\n<p>&nbsp;<\/p>\n<p>Let&#8217;s understand the basics of an <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-offset-function-introduced\/\" target=\"_blank\" rel=\"noopener\"><strong>OFFSET<\/strong><\/a> Function:<\/p>\n<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>It returns a reference to a range, from a starting point to a specified number of rows, columns, height, and width of cells<\/p>\n<p><em><strong>Formula breakdown:<\/strong><\/em><\/p>\n<p>=OFFSET(<span style=\"color: #008000\">reference<\/span>, <span style=\"color: #ff0000\">rows<\/span>, <span style=\"color: #ff6600\">columns<\/span>, <span style=\"color: #0000ff\">[height]<\/span>,<span style=\"color: #800080\"> [width]<\/span>)<\/p>\n<p><em><strong>What it means:<\/strong><\/em><\/p>\n<p>=OFFSET(<span style=\"color: #008000\">start in this cell<\/span>, <span style=\"color: #ff0000\">go up\/down a number of rows<\/span>, <span style=\"color: #ff6600\">go left\/right a number of columns<\/span>, <span style=\"color: #0000ff\">height of range<\/span>, <span style=\"color: #800080\">width of range<\/span>)<\/p>\n<hr \/>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/offset-function\/\" target=\"_blank\" rel=\"noopener\">The OFFSET function in Excel<\/a> 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.<\/p>\n<p><span style=\"text-decoration: underline;color: #0000ff\"><a style=\"color: #0000ff;text-decoration: underline\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-offset-function-introduced\/\" target=\"_blank\" rel=\"noopener noreferrer\">Click here to see how the formula works visually &amp; interactively\u00a0<\/a><\/span><\/p>\n<p>&nbsp;<\/p>\n<h3>Now that you are familiar with the OFFSET function, let&#8217;s move ahead and understand how to show Excel Sum Last 7 Entries in a Row.<\/h3>\n<p>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.<\/p>\n<p>Watch Excel sum last n values in a row on <a href=\"https:\/\/youtu.be\/FvZ5Kvsj9bg\" target=\"_blank\" rel=\"nofollow noopener\"><strong>YouTube<\/strong><\/a> and give it a thumbs-up!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/FvZ5Kvsj9bg?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"width: 0px;overflow: hidden;line-height: 0\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p>Follow the<strong>\u00a0step-by-step tutorial<\/strong> on How to show Excel sum last n rows \u00a0and <strong>download this Excel workbook<\/strong>\u00a0to practice along:<\/p>\n<p><strong><span style=\"text-decoration: underline;color: #0000ff\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff;text-decoration: underline\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/Offset-Last-7-Transactions.xlsx\" rel=\"\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Offset-Last-7-Transactions.xlsx<\/span><\/a><\/span><\/strong><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong> <\/span>We need to <strong>enter the <em>Sum<\/em> function<\/strong> as we are going to Sum the last 7 transactions<\/p>\n<p>(We can also add the <em>Average<\/em> function if we want to show the Average of the last 7 transactions):<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sum-last-7-transactions-offset-function-excel\/#SUM\" title=\"=SUM\">=SUM<\/a><\/li><\/ul><\/nav><\/div>\n<h2 style=\"text-align: center\"><span class=\"ez-toc-section\" id=\"SUM\"><\/span><span style=\"color: #0000ff\">=SUM<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong><\/span> We need to <strong>enter the Offset function<\/strong> so we can reference the range that we want to Sum:<\/p>\n<h2 style=\"text-align: center\">=SUM<span style=\"color: #0000ff\">(OFFSET<\/span><\/h2>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3:<\/strong><\/span> 1st Offset argument &#8211; <strong>Where do we want to to start our reference?<\/strong><\/p>\n<p>This is at the start of the Daily Sales list:<\/p>\n<h2 style=\"text-align: center\">=SUM(OFFSET(<span style=\"color: #0000ff\">D11<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-1st-argument.jpg\" rel=\"attachment wp-att-2718\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone size-full wp-image-2718\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-1st-argument.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"563\" height=\"139\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-1st-argument.jpg 563w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-1st-argument-300x74.jpg 300w\" sizes=\"(max-width: 563px) 100vw, 563px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 4:<\/strong> <\/span>2nd Offset argument &#8211; <strong>How many Rows down do we want to go?<\/strong><\/p>\n<p>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.<\/p>\n<p>So as we are starting at cell D11, we are going to go down 11 cells ( <em>COUNTA(D11:D100)<\/em> ) and end up after\u00a0the last cell with a value.<\/p>\n<p>NB: It is always a good idea to enter an ending range that is more than your last data cell. \u00a0That way as your new data get entered, we will be sure to capture it!<\/p>\n<h2 style=\"text-align: center\">=SUM(OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"> COUNTA(D11:D100)<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argumentV2-1.jpg\" rel=\"attachment wp-att-2851\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone size-full wp-image-2851\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argumentV2-1.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"674\" height=\"377\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argumentV2-1.jpg 674w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argumentV2-1-300x168.jpg 300w\" sizes=\"(max-width: 674px) 100vw, 674px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 5:\u00a0<\/strong><\/span><strong>How many of the last sales transactions do we want to Sum?<\/strong> \u00a07<\/p>\n<p>So we need to take away 7 from the COUNTA formula which means that our Offset function will go up\u00a0by 7 cells:<\/p>\n<h2 style=\"text-align: center\">=SUM(OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNTA(D11:D100)<\/span>-7,<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argument-minus-7_v2-1.jpg\" rel=\"attachment wp-att-2745\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone size-full wp-image-2745\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argument-minus-7_v2-1.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"593\" height=\"336\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argument-minus-7_v2-1.jpg 593w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-2nd-argument-minus-7_v2-1-300x170.jpg 300w\" sizes=\"(max-width: 593px) 100vw, 593px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 6:<\/strong> <\/span>3rd Offset argument &#8211; <strong>How many Columns to the right\/left do we want to move?<\/strong><\/p>\n<p>We do not want to move to any Columns, so we simply enter 0<\/p>\n<h2 style=\"text-align: center\">=SUM(OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNTA(D11:D100)-7,<\/span>0,<\/span><\/h2>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 7:<\/strong><\/span> 4th Offset argument &#8211; <strong>How High do we want our referenced data to be?<\/strong><\/p>\n<p>Since we want to Sum 7 transactions, then we need the [height] to be 7 cells high:<\/p>\n<h2 style=\"text-align: center\">=SUM(OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNTA(D11:D100)-7,0,<\/span>7,<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-3rd-argument.jpg\" rel=\"attachment wp-att-2730\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone size-full wp-image-2730\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-3rd-argument.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"669\" height=\"332\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-3rd-argument.jpg 669w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-3rd-argument-300x149.jpg 300w\" sizes=\"(max-width: 669px) 100vw, 669px\" \/><\/a><\/p>\n<p style=\"text-align: left\"><span style=\"color: #ff0000\"><strong>STEP 8:<\/strong><\/span> 5th Offset argument &#8211; <strong>How Wide\u00a0do we want our referenced data to be?<\/strong><\/p>\n<p style=\"text-align: left\">We want to reference 1 Column only, so we need to enter 1 for the [width]<\/p>\n<h2 style=\"text-align: center\">=SUM(OFFSET(<span style=\"color: #000000\">D11,<\/span><span style=\"color: #0000ff\"><span style=\"color: #000000\"> COUNTA(D11:D100)-7,0,7,<\/span>1))<\/span><\/h2>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-4th-argument.jpg\" rel=\"attachment wp-att-2731\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone size-full wp-image-2731\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-4th-argument.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"665\" height=\"337\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-4th-argument.jpg 665w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-4th-argument-300x152.jpg 300w\" sizes=\"(max-width: 665px) 100vw, 665px\" \/><\/a><\/p>\n<p style=\"text-align: left\">As we can see the Sum of the last 7 transactions from our example is $28,472.<\/p>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-1.jpg\" rel=\"attachment wp-att-2735\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone size-full wp-image-2735\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-1.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"482\" height=\"337\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-1.jpg 482w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer-1-300x210.jpg 300w\" sizes=\"(max-width: 482px) 100vw, 482px\" \/><\/a><\/p>\n<p style=\"text-align: left\">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 \ud83d\ude42<\/p>\n<p style=\"text-align: center\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer2-1.jpg\" rel=\"attachment wp-att-2736\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Sum the Last 7 Transactions with the Offset Function\" class=\"alignnone wp-image-2736 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer2-1.jpg\" alt=\"Sum the Last 7 Transactions with the Offset Function\" width=\"481\" height=\"386\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer2-1.jpg 481w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/offset-answer2-1-300x241.jpg 300w\" sizes=\"(max-width: 481px) 100vw, 481px\" \/><\/a><\/p>\n<p>This completes our tutorial on Excel sum last 7 values in row!<\/p>\n<p><span style=\"color: #000000\"><strong>Further Learning:<\/strong><\/span><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/offset-function-in-excel\/\" target=\"_blank\" rel=\"noopener\">How to use OFFSET Function in Excel<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/return-the-last-value-column-offset-function-excel\/\" target=\"_blank\" rel=\"noopener\">Return the Last Value in a Column with the Offset Function<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/create-dynamic-data-range-offset-function-excel\/\" target=\"_blank\" rel=\"noopener\">Create a Dynamic Data Range with the OFFSET function<\/a><\/li>\n<\/ul>\n<p>Make sure to download our FREE PDF on the<strong><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer nofollow\">\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you will learn how to show Excel Sum Last 3 Entries in a Row using the OFFSET function. &nbsp; Let&#8217;s understand the basics of an OFFSET Function: 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17171,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Sum the Last 7 Transactions with the Offset Function","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,11,281],"tags":[43,79,123],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2715"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=2715"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2715\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17171"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=2715"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=2715"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=2715"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}