How to exclude hidden rows from SUM
SUM function always calculates with all cells in the range. Sometimes, it’s not desired behavior and we want to exclude hidden cells. Continue reading “How to exclude hidden rows from SUM” »
Tags: SUBTOTAL
How to exclude hidden rows in pivot table
Normally, pivot table counts the hidden rows. We have to add a flag to each row to determine if it is hidden or not. How to do that shows this example. Continue reading “How to exclude hidden rows in pivot table” »
Tags: Pivot Table, SUBTOTAL
How to display the current totals in the filter
Common Excel functions like SUM, AVERAGE, MAX, MIN or COUNT always calculate with the range of cells that you give to them. It does not matter if cells are visible or not. When we use the filter to select some of data, we want to calculate only the visible cells. There is one nice function called SUBTOTAL that solves this. It can replace 11 other functions and you can choose if it calculates hidden cells or not. Continue reading “How to display the current totals in the filter” »
Tags: SUBTOTAL
