You can find the INDEX/MATCH tutorial that includes summing the rows or columns here.īut what if you don't want to sum the entire row or column of data from the array, but just a portion, and you want that range to be dynamic, so you can choose the values you want to SUM. You can find the basic INDEX/MATCH tutorial here. Another tutorial showed how we can also sum either that row or column using the same functions. In a previous tutorial we used the traditional INDEX and MATCH function combination to pull the data from the intersection of a row and column.
HOW TO SUM A COLUMN IN EXCEL THAT IS FILTERED DOWNLOAD
If you get a preview, look for the download arrow in the upper right hand corner. You can download the file here and follow along. Cells hidden with the Hide Columns command are included in the result, as Dick Kusleika mentioned in the comments in his Determine If Cell is Hidden in VBA blog post.In this tutorial we are going to explore a variation of the traditional INDEX/MATCH combination to extract and sum only a portion of a row or column within an array. Remember that only the Hide Rows command affects the SUBTOTAL result, not the Hide Columns command. However, the SUBTOTAL(9 formula treats that hidden row as filtered, and doesn’t include it in the result. In this example, the upper list is filtered, and a row is manually hidden in the table below. The final quirk, that I just discovered, is that a table below the filtered list is also affected by the filter status. Excel doesn’t seem to distinguish between the two types of non-visible rows. Even though one row has been manually hidden, the SUBTOTAL function in D7 recognizes it as a filtered row, because it is hidden in a filtered list.Īnother “feature” of filtered lists is that if you manually hide rows, they can be automatically unhidden, if you clear the filter. In this case, the SUBTOTAL functions in columns D and E both return the same result. In theory, the SUBTOTAL(9 and SUBTOTAL(109 should return different results. In the example below, there is a filter applied, to hide the Pen Sets item. In cell E7, the result is 30, because the 109 argument has been used.
In the example where row 3 has been manually hidden, if the first argument is changed from 9 to 109, the manually hidden amounts are not included in the SUBTOTAL result. If you use the arguments in the 100s, neither filtered rows nor manually hidden rows are included in the result. In Excel 2003, and later versions, the same set of functions is repeated, starting with 101 – AVERAGE. As you can see in the list before, there are 11 functions, listed in alphabetical order, starting with 1-AVERAGE. The first argument in the SUBTOTAL function specifies which function should be used.
In the screen shot below, row 3 is hidden, but the SUM and SUBTOTAL functions both show a total of 40. If you hide rows by using the Hide Row command, those amounts might be included in the total, even if you use the SUBTOTAL function.
When numbers are hidden with the filter, the SUBTOTAL function sums only the numbers in the visible rows. If the table has a filter applied, the SUBTOTAL function will be automatically inserted, instead of the SUM function. There are a couple of quirks with SUBTOTAL though, including one strange thing that I hadn’t noticed before.Īfter you filter the rows in a list, use the AutoSum button on the Excel Ribbon’s Home tab, to insert a sum function. With the SUBTOTAL function, you can solve that problem, and only sum the visible numbers. Even though some of the numbers are hidden, they’re still included in the total. The SUM function doesn’t do such a great job if you’ve hidden some of the rows with a filter, or with the Hide Rows command. But SUM might not be the best function to use in all situations where you need a total. The Excel SUM function does a great job of adding numbers on a worksheet, and it’s probably the first Excel function that you learned about.
0 kommentar(er)
