Excel - Math functions on filtered columns

 

I needed to perform some math on filtered columns in Excel. To do that I needed to use the SUBTOTAL function. 

1) Counting how many items in a filtered column

=SUBTOTAL(3,A3:A999999)

or

=CONCAT("Num Sites: ",SUBTOTAL(3,A3:A999999))

2) Adding values in a filtered column. I also needed to convert GB to TB so I needed to do additional math.

=ROUND(SUBTOTAL(109,F3:F99999)/1024,3)

or
=CONCAT(ROUND(SUBTOTAL(109,F3:F99999)/1024,3)," TB Used")

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.