Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: SUBTOTAL

Count visible rows in a filtered list in Excel

This tutorial shows how to Count visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(3,range) Explanation If you want to count the number of visible items in a filtered list, you can use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter. The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN,…

Count visible rows only with criteria in Excel

This tutorial shows how to Count visible rows only with criteria in Excel using the example below; Formula =SUMPRODUCT((range=criteria)*(SUBTOTAL(3,OFFSET(range,rows,0,1)))) Explanation To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET. The problem The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden rows. However, it isn’t able…

Sum visible rows in a filtered list in Excel

This tutorial shows how to Sum visible rows in a filtered list in Excel using the example below; Formula =SUBTOTAL(9,range) Explanation If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered out), you can use the SUBTOTAL function with function number 9 or 109. What makes SUBTOTAL especially useful is that it automatically…