Dynamic Ranges with OFFSET and COUNTA Functions in Excel

Looking for ways to create dynamic range by applying OFFSET and COUNTA functions in Excel? Don’t worry! You have landed in the right place. In this article, I will show you how to create dynamic ranges OFFSET function and COUNTA function in Excel.

Introduction to OFFSET and COUNTA Functions

The OFFSET function returns a reference to a range that is a given number of rows & columns from a given reference.

The syntax of the OFFSET function is given below:

The COUNTA function counts the number of empty cells in a given range, The syntax of the COUNTA function is given:

We will implement these two functions to create dynamic range in Excel.

How to Create Dynamic Range by Using OFFSET and COUNTA Functions in Excel: 2 Ways

Let’s say, we got a dataset of some information about Car (i.e. Car Type, Model, Year, VIN). The dataset includes information about 13 types of cars and row 5 to 17 describes them.

We want to make the data range dynamic so that any newer input changes the range. For this, we will apply the OFFSET and the COUNTIF functions.

In this section, you will find 2 effective ways to create dynamic ranges with OFFSET and COUNTIF functions in Excel. I will demonstrate them one by one here. Let’s check them now!

1. Creating Dynamic Range with OFFSET and COUNTA Functions

Let’s make our concerning dataset dynamic with the relevant functions. After making it dynamic, we will check whether any newer input in the data range changes the table or not.

Just proceed with the following steps.

⏩ Steps:

• First of all, select a cell where you want to re-input the dynamic data. The static data will be converted to a dynamic state after applying the relevant Excel function in it. Now, type the formula below in the selected cell.

Here,

• B5 = Reference (based on which the offset parameters are applied)
• 0 = Row number counted from the referring cell
• 0 = Column number counted from the referring cell
• COUNTA(B5:B50) = Height (Number of rows that will return as resultant values)
• COUNTA(B5:E5) = Width (Number of columns that will return as resultant values)

• Then, press ENTER and a new data range will be shown as per your arguments application. Thes newly shown data range is dynamic in nature.

Now, let’s check whether it has been converted to the Dynamic range or not. Enter a new value (i.e. Latex) in the Main Data in cell B19. Don’t input anything else. As soon as you entered the new value, the data of the Offset Result will show 0 for the cells (i.e. H19, I19, J19) in which you didn’t enter any value. The auto change in these cells indicates that the range is now converted to a Dynamic state.

Read More: Difference Between COUNT and COUNTA Functions in Excel

2. Using Name Manager with OFFSET and COUNTA Functions

Now, we will make this data dynamic by applying another command: “Name Manager”. The corresponding functions will also also be used along with the Name Manager command. In order to implement this task, proceed in the steps below.

⏩ Steps:

• First, select the first cell of this data range, go to the Formulas tab> click the Name Manager option under the Define Names group.

• Then, a dialog box with the heading Name Manager will appear in the window. Click the New field in the dialog box.

• After that, the New Name dialog box will show up. Assign a name in the Name field and enter the following formula in the Refers to field.

Here, Sheet5 is the worksheet in which we performed the task.

• Click OK.

• Now, Excel will return you back to the Name Manager command box. Close the box and switch to the Excel worksheet window.

• Here, select a cell where you want to paste the Named Range you have you have created a few times ago. Just start typing the name of the range (i.e. Car_info) and Excel will suggest you the Named Range. Double-click on the range

• Hence, you get the Named Range in the selected cell and at the same time the range has been made dynamic.

Check this like the previous method whether it is dynamic or not.

Read More: [Fixed] Excel COUNTA Function Not Working

Use of Excel Dynamic Ranges for Calculation

We can use the dynamic ranges made with OFFSET and COUNTA functions for calculations. Let’s deal with the dataset below. In column D, the Price of the Car is stated. Make the data range dynamic just like Method 1.

We will calculate the Total Price, Average Price, Maximum Price, and  Minimum Price from this dynamic range. So, let’s start the process like the one below.

⏩ Steps:

• First of all, let’s calculate the Total Price of the dynamic range. Apply the SUM function for this.

Here, the range of summing is considered as J5:J50 so that it can calculate the sum up to cell J50 from the dynamic range after entering values in it. You will get the total value of the dynamic range J5:J50.

• Similarly, apply AVERAGE, MAX, and MIN functions to calculate relevant values.

Now, let’s check whether the calculated values change with newer inputs. Enter a new Car information whose price is $376700. It is the highest price in the range.  Hence, you will notice that the Maximum Price as well as all other prices has been changed now.

Practice Section

Here, I am giving you a practice section so that you can practice yourself.

Download Practice Workbook

You can download the practice book from the link below.

Conclusion

In this article, I have tried to show you some methods to create a dynamic range with OFFSET and COUNTA function in Excel and some uses of it. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.  Have a great day!

Related Articles

• How to Use COUNTA from SUBTOTAL Function in Excel
• How to Use COUNTA Function with Criteria in Excel