How to remove a row from a calculation on Excel?

[Petemcg]

Basically a part of my spreadsheet is the image below:

http://i.imgur.com/lN46kUM.png

Now, on the cell marked by green, I want a yes/no option which will either include or exclude all of the B2:J2 cells (seen in blue) from the Sub-total calculation marked in red.

Ideas???

Thanks a lot

I originally posted this on yahoo but thought that this forum would certainly be a lot more reliable!

[martindwilson]

well the option already exists just use filter and exclude the row

[Petemcg]

well the option already exists just use filter and exclude the row

Maybe I'm not understanding correctly, because although this would filter out my desired rows, the sub-total result will not change. Because the filter is just sort of "hiding" the result (as far as I am aware), but is still included in my calculation. Why I needed a Yes/No option was because this would just treat the whole column as effectively zero (when I don't need it) and 1 (when I do) [and thus change what the sub-total is].

[AlKey]

Maybe this:

=SUBTOTAL(109,B1:B3)

[Petemcg]

Maybe this:

=SUBTOTAL(109,B1:B3)

Isn't this just summing the cells B1:B3? I need a function which will exclude a row from my calculation when I click "No" on a drop down menu. It takes too long to exclude/include it manually each time

[AlKey]

if you apply filter, this formula will sum only visible cells and ignore hidden.

Please see attached file with Group feature (on the left) where you easily hide/unhide first row.

[Petemcg]

if you apply filter, this formula will sum only visible cells and ignore hidden.

Please see attached file with Group feature (on the left) where you easily hide/unhide first row.

Thank you very much!