Subtotal on filtered data

Tested in Excel 365 (16.8730.2046) 64-bit. All links open in new tab.

When you have a table for which you have to display a subtotal conditioned by values found in two columns and the summary numbers should change when you filter the data you an use a formula like this:

  1. =SUMPRODUCT(--(CondRange="Cond"),--(CondRange2="Cond2"),SUBTOTAL(109,OFFSET(RangeToSum,ROW(RangeToSum)-MIN(ROW(RangeToSum)),0,1)))

In this file the formula looks like:

  1. =SUMPRODUCT(--($A$2:$A$17=$A21),--($B$2:$B$17=$B21),SUBTOTAL(109,OFFSET(F$2:F$17,ROW(F$2:F$17)-MIN(ROW(F$2:F$17)),0,1)))


Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.