For summing values without the subtotals in Excel, what would you do? In this article, we will show you a quick method to achieve it.įilter multiple columns simultaneously in Excel Supposing you have a list of data mixed with several subtotal cells, when you need to sum the total, all the subtotals are included in the final summing. Sum values without or exclude subtotals in Excel How do you delete the visible rows or columns without deleting the hidden rows or columns in Excel? This tutorial will introduce the ways to delete visible rows only. In Excel, we usually hide some important rows or columns of information, sometimes, we need to delete the visible rows or columns only, if you delete them with Delete key directly, the hidden values will be deleted at the same time. Only delete visible rows or columns in Excel You can change the number 9 to any number mentioned in the below table based on your needs.įrom now on, when you filter the column data, the SUBTOTAL function only sum the visible cells as the below screenshot shown.In the formula, number 9 means that you specify the sum function to the subtotal C2:C13 is the range you will subtotal.Select a blank cell, copy the below formula into it and press the Enter key. Ref2 (Optional): A named range or reference you want to subtotal.ġ.Ref1 (Required): A named range or reference you want to subtotal.Funtion_num (Required): A number from 1 to 11 or 101 to 111 that specifies the function to use for the subtotal.Subtotal only visible cells after filtering with an amazing toolĪctually, the Subtotal function can help you to sum only the visible cells after filtering in Excel. Subtotal only visible cells after filtering with the Subtotal function If you only want to subtotal the visible cells after filtering, methods in this tutorial will help you.
When filtering data in the column, the Sum function adds both the visible cells and the hidden cells. Supposing you have calculated the total value from a column with the Sum function.
How to subtotal data in excel and show only the result how to#
How to subtotal only the visible cells after filtering in Excel?
0 kommentar(er)
