sinax.blogg.se - Multiple subtotals in excel 2010

Aggregate Only the Visible Rows in the Subtotal Function Excel That means the Subtotal function in Excel excludes subtotals in the range to evaluate. If you use the Sum function in the same range, the result will be 720. The range (ref1) in that formula is C2:C13. We must pay attention to the one in cell C14. Please refer to the second column in the table above. In the following Excel formula example, I have used the function number 9 (sum). Exclude Subtotals in Subtotal Function in Excel You can understand the difference in the examples below. The first group from 1 to 11 and the second group from 101 to 111. There are two groups of function numbers. Syntax: SUBTOTAL(function_num,ref1,…) Function Nameįunction Numbers to Exclude Filtered-Out Rows & Aggregate Only the Visible Rows in the Subtotal Function Excel.

Exclude Subtotals in Subtotal Function in Excel.

Select the cell where you want to paste the subtotals.

Press Ctrl+C to copy the rows to the Clipboard.

Excel selects just the visible information from the range you specified in step 3.

Make sure the Visible Cells Only option is selected.

Excel displays the Go To Special dialog box. (Do not select complete rows or columns just select the range of cells.)

Select the range of cells containing the subtotals you want to copy.

(Click on the small 2 in the outline levels shown at the top of the gray area at the left of the worksheet.)

Collapse the information in the list so that only the subtotals are showing.

Add your worksheet subtotals as you normally would.

This is actually rather easy to do, if you follow these steps: (How to create subtotals is covered in other issues of ExcelTips.) You may be wondering, once the subtotals are in place, how you can copy the subtotals to a different worksheet. Excel has a handy feature that allows you to automatically add subtotals to a data list.