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. 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.
By adding the SUM function to this formula, we can now add up the cells that Excel has displayed via OFFSET. Whenever cells are added or removed from the range, the COUNTA section of the formula is updated automatically. Next, we added the COUNTA function to provide the Height of the area for OFFSET. 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 We started by using the OFFSET function in the formula. 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. Now that we understand the OFFSET section of the formula we add the SUM function to it to complete the overall formula giving us: Finally, we advise Excel of the Width, which is in this case 1 column. Should we add or remove rows, this will adjust automatically! For the Height, we are telling Excel that the height is the result of the COUNTA formula which is currently 29. Now comes the dynamic part of the whole formula. 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). The number of Rows we are asking Excel to move from the starting cell of C3 is 0. We will begin by looking at the OFFSET section of the formula: Now, we include the COUNTA formula above as part of our OFFSET formula to create a dynamic range. 
Example of the COUNTA function used in a formula in Excel 2007 and Excel 2010.
0 kommentar(er)
