I need a formula to give me a sum total based on the visible data because I am using a filter. I know the subtotal command does this but I also need to set a criteria. If I use the sumifs only it adds up all data even filtered out data =(SUMIFS(F7:F20746,K7:K20746,"Y")) but I only want the filtered data. Logic: sum up range F7:F20746 if range k7:k20746 = "Y"

This is what I tried but it gives me an error:

=SUBTOTAL(3,(SUMIFS(F7:F20746,K7:K20746,"Y")))

message edited by mecerrato

I won't take credit for this, but I will take credit for knowing when to use Google. ;-) Stolen without permission from then following site and then modified to match your ranges:

http://www.mrexcel.com/forum/excel-...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F7:F20746,ROW(F7:F20746)-MIN(ROW(F7:F20746)),,1)),--(K7:K20746="Y"),F7:F20746)

Don't ask me how it works, I haven't spent any time reverse engineering the formula. If you really want to know how it works, use the Evaluate Formula feature to single step through the formula. If you plan to try that, I suggest that you shorten the range to just a few cells to make it easier to follow.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

It works like a charm, thanks

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History