Can you combine SUMIF and SUBTOTAL

[violet1905]

Hi,

I have a table that I am using AutoFilter on and then the SUBTOTAL function to see my totals of things I filter by. I then have a SUMIF formula to total by a type column. My problem is when I filter my SUMIF formula still includes the hidden rows. Is there a sumif formula that would not include the filtered out rows? I've tried combining subtotal and sumif, but it didn't work...

Thanks for any help.

 

[barry houdini]

If you want to sum for type "x" where types are in A1:A100 and numbers in B1:B100 you can use this formula to only sum for the visible rows

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B1:B100,ROW(B1:B100)-ROW(B1),0,1,1)),--(A1:A100="x"))

 

[violet1905]

Wow. Thank you - I'll try it!

 

[Skipper Skræk]

If you want to sum for type "x" where types are in A1:A100 and numbers in B1:B100 you can use this formula to only sum for the visible rows

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B1:B100,ROW(B1:B100)-ROW(B1),0,1,1)),--(A1:A100="x"))

Great combination, thanks

 

[sevenhvn]

If you want to sum for type "x" where types are in A1:A100 and numbers in B1:B100 you can use this formula to only sum for the visible rows

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B1:B100,ROW(B1:B100)-ROW(B1),0,1,1)),--(A1:A100="x"))

A whole decade later ... worked great! Thank you!

 

[djrksaurabh]

kudos.........

If you want to sum for type "x" where types are in A1:A100 and numbers in B1:B100 you can use this formula to only sum for the visible rows

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B1:B100,ROW(B1:B100)-ROW(B1),0,1,1)),--(A1:A100="x"))