AutoSum/Subtotal Inclusions/Exclusions

E

evoxfan

In column "I" I have either "True" or "False" in each cell.
I use autofilter and filter so that only "True" values are showing.

In Cell I55, I have a formula (=SUBTOTAL(109,F3:F53)) to sum all values in
Column F while the filter is applied and the formula works great. However
when I remove the filter my formula now includes all the values that were
once hidden.

Any suggestions on how to prevent this from happening so that it does not
include previously hidden values when my filter is removed?

Thanks in advance!
 
L

Luke M

Either copy the value of the formula somewhere before you un-filter (either
manually, or through a macro)

or, if you're only filtering the one criteria, why not use:
=SUMIF(A3:A53,"True",F3:F53)

(I'm assuming column A contains true/false criteria)
 
G

Gord Dibben

Copy>Paste Special>Values>OK>Esc is the only way I can think of without
using a formula that doesn't include Subtotal function.

=SUMIF(I3:I53,"True",F3:F53)

Or if True or False are formula-derived you can remove the quotes

=SUMIF(I3:I53,TRUE,F3:F53)


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top