Use OFFSET and COUNTA to create dynamic ranges with automatically updating formulas in Excel 2007 and Excel 2010

Updated on April 26, 2013

Introduction

Welcome to my latest hub on Excel. Today, we are going to look at something that is rather cool and something I have needed in my own Excel Spreadsheets for some time.

Imagine if you will, a spreadsheet where you record invoices, sales, web hits etc and then SUM them to give yourself a running total. Every time you add extra rows to your spreadsheet you have to update your SUM formula to allow for the extra items.

Well not anymore! Welcome to the dynamic range using the OFFSET and COUNTA functions. Using this function, whenever rows are added (or deleted) and also each time copied cells are inserted, the SUM formula will automatically and dynamically update to reflect this.

Using the OFFSET function in a formula in Excel 2007 and Excel 2010

The OFFSET function allows you to ask Excel to tell you the contents of a cell (or a range of cells) a certain number of rows and columns away from a reference cell (or cells).

If you use OFFSET on its own, it will return the value of the cell you referred it to. We will start with that as an example:

=OFFSET(B3,1,1,1,1)

Example of how to use the OFFSET function in a formula in Excel 2007 and Excel 2010.
Example of how to use the OFFSET function in a formula in Excel 2007 and Excel 2010. | Source

The formula is made up of the following parts:

  • The cell you are asking Excel to start from (in this instance B3) (known by Excel as the Reference)
  • The next part of the formula is the number of Rows (which can be either up or down) from the reference cell to the cell you are looking at. We are interested in cell C4 so this value is 1 as it is one row down from cell B3.
  • Next, we need to know how many Columns from the starting to the reference cell. This is 1 again as it is one column from B3 to C4
  • The fourth element of the formula is Height. This is the number of rows you want the returned data to be.
  • Finally, we have Width or the number of columns you want the data to be.

Note: Rows and Columns can be a negative or positive number. Please refer to the below table to understand when to use positive or negative values.

When to use Positive and Negative Numbers for Rows and Columns in OFFSET formulas

 
 
 
 
Row
Column
Positive
Below Reference
Right of Reference
Negative
Above Reference
Left of Reference

Note: Height and Width must both be positive. If they are not specified, Excel will use the height and width of the reference.

Note: If you get a #VALUE you have typically asked Excel to display something it cannot display.

To illustrate this:

If I use the following formula:

=OFFSET(B3,1,1,2,1)

I am asking Excel to display the results of one cell in two cells which it cannot do so it returns an error.

Example of a #VALUE error received when using the OFFSET function in a formula in Excel 2007 and Excel 2010.
Example of a #VALUE error received when using the OFFSET function in a formula in Excel 2007 and Excel 2010. | Source

Note: If you get a #REF error you have gone off the edge of your worksheet.

This formula results in a #REF error, as there is no such row four rows above B3.

=OFFSET(B3,-4,2,1,1)

Example of a #REF error received when using the OFFSET function in a formula in Excel 2007 and Excel 2010.
Example of a #REF error received when using the OFFSET function in a formula in Excel 2007 and Excel 2010. | Source

Using OFFSET with COUNTA in a formula to create a dynamic range in Excel 2007 and Excel 2010

Now that we understand the OFFSET function and how to use it in a formula, we get to the really interesting part of this hub; we get to make a dynamic range using it alongside COUNTA. We can then SUM the values in the dynamic range and this formula will automatically recalculate if cells are added, removed or their contents changed. Of course, SUM is just an example that I chose for this hub, you could use AVERAGE, MAX or any other function you felt was appropriate for your needs alongside the OFFSET and COUNTA functions.

First, let’s quickly look at what COUNTA does for us in this example:

It simply counts the number of cells that are not blank between the start and end cell (in this case there are 29 non blank cells between cell C3 and cell C32).

=COUNTA(C3:C32)

Example of the COUNTA function used in a formula in Excel 2007 and Excel 2010.
Example of the COUNTA function used in a formula in Excel 2007 and Excel 2010. | Source

Now, we include the COUNTA formula above as part of our OFFSET formula to create a dynamic range. The finished formula is:

=SUM(OFFSET(C3,0,0,COUNTA(C3:C32),1))

We will begin by looking at the OFFSET section of the formula:

OFFSET(C3,0,0,COUNTA(C3:C32),1)

  • We start at cell C3 as our Reference
  • The number of Rows we are asking Excel to move from the starting cell of C3 is 0
  • Likewise, the number of Columns is also 0 (I like to add and remove columns to my data so keeping it at 0 means that the formula will never break)
  • Now comes the dynamic part of the whole formula. For the Height, we are telling Excel that the height is the result of the COUNTA formula which is currently 29. Should we add or remove rows, this will adjust automatically!
  • Finally, we advise Excel of the Width, which is in this case 1 column.

Now that we understand the OFFSET section of the formula we add the SUM function to it to complete the overall formula giving us:

=SUM(OFFSET(C3,0,0,COUNTA(C3:C32),1))

If we now Insert an additional blank row you will notice that the Total Sales goes down by 18 (which is the value in cell C32 which is the final row) from 1,085 in the figure above to 1,067 in the figure below.

Inserting a blank row into a dynamic range will lead to formulas being inaccurate in Excel 2007 and Excel 2010.
Inserting a blank row into a dynamic range will lead to formulas being inaccurate in Excel 2007 and Excel 2010. | Source

I now add a value into the blank row and the Total Sales is now correct and includes the value in C32.

With data entered into cell C19, the formula calculating the values in the dynamic range in Excel 2007 and Excel 2010 is now correct.
With data entered into cell C19, the formula calculating the values in the dynamic range in Excel 2007 and Excel 2010 is now correct. | Source

Note: Inserting a blank row into your dynamic range will lead to the results shown in your SUM formula being incorrect.

Note: Should you add additional rows, for example by Inserting Copied Cells Excel will simply increase the COUNTA part of the formula which makes it dynamic.

By way of an example, I added four rows to my spreadsheet and Excel updated my formula automatically!

=SUM(OFFSET(C3,0,0,COUNTA(C3:C36),1))

Conclusion

The ability to have dynamic ranges in your Excel spreadsheets containing data such as sales, invoices, web hits etc which can then be added up or averaged is a very useful thing indeed. This means that you no longer have to manually update your formulas whenever you make additions or deletions to your data.

To achieve this:

  • We started by using the OFFSET function in the formula. This function allows you to start with a reference cell (or cells) and ask Excel to display the contents of another area a specific number of rows and columns from the reference cell
  • Next we added the COUNTA function to provide the Height of the area for OFFSET. Whenever cells are added or removed from the range, the COUNTA section of the formula is updated automatically.
  • By adding the SUM function to this formula, we can now add up the cells that Excel has displayed via OFFSET.

When all of these functions are added to a formula, it will now dynamically sum a range of cells, updating automatically when data is added or deleted from the range. This for me is the beauty and power of Excel, the fact that you can add two functions together and create something that is not only very useful, but also more than the sum of its parts.

I hope that you have found this hub useful and informative and that using OFFSET will transform your spreadsheets in the same way that it has transformed mine. Many thanks for reading, please feel free to leave any comments you many have below.

And Finally....

Has OFFSET transformed the way you work with data in Excel?

See results

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile image

        Ashish 

        12 months ago

        Thanks for really a nice explanation. Could you also suggest how to make 'COUNTA' function to exclude NA() values?

      • profile image

        jen 

        17 months ago

        It looks like a clutter. cant you add a spreadsheet reflecting your observations... That would have made more sense than this illogical way of workouts

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)