Sum of column items respecting the filter

Hi Deckey, there are a couple options.

The first, is to apply the filter, highlight the cells you want to SUM and look at the bottom right of your screen. There are automatic formulas that can perform quick calculations for you.

Another option is to use a SUMIF which can sum a group of cells if they meet a certain criteria.

Check out our Help Center article on formulas for help setting this up: http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas

this seems like an awkward work around and frankly not really that dynamix.  I kind of defeats the whole purpose of the filter if you still need to go back and check them all off.  

Are there any other possible solutions.  Seems like this is a function that would be pretty desirable across a wide range of users.

I was able to accomplish a pretty intuitive means in which to accomplish the OP's intent through some formula trickery and an intentional SS layout.  The context of this dialog is with a running ledger of multiple Project's and Exercise's orders on a given Program, with tracking of booked projects, invoices, receipts, and costs.

A graphical image referenced in the following is available here: http://i.imgur.com/icw0dyw.png

The SS is structured with a typical running ledger, with row 2 being the top-level parent.  This is intentional, as row 1 is the 'query' row (green arrow).  When either or both of the two 'query' cells (red circle) is selected, formulas serve to provide subtotals and relevant information to the right of the Project/Exercise dropdowns (in the row 1 cells in the red square).

To make use of this query row, I first select the desired parameters in either of the Project1 and Exercise1 cells (or both, if higher fidelity filtering needed - Orange Arrow in Red Circle) and then apply SS Column Filters to match that which I selected in the dropdows (Blue Arrow - filters selected without parent rows being displayed).

The results, once the SS Column Filters is applied, is depicted here: http://i.imgur.com/LzMTtEi.png

The results of the filtering is presented below the summary row, which provides the subtotals of only the filtered data presented.  Misson Accomplished.

A side benefit is that a simple 'Project'/'Exercise' fiscal health check can be made by just selecting the dropdownss in Row1 (Project1, Exercise1).  This enables the PM to quickly assess the fiscal parameters on an overall project basis, as well as for each exercise therein (without having to apply filters).  And, the Grand Total Parent Row on the unfiltered SS serves to give a quick fiscal health check across the whole of the Program, inclusive of all Projects/Exercises.

So, to help with replicating this, should someone desire to do so, I offer the following (error avoidance baked into the formulas a bit):

Doc_Ref_#1 (Percent invoiced text): =IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(OR(BOOKED1 = 0, BOOKED1 = ""), "No Booking", LEFT(ROUND(INVOICED1 / BOOKED1, 3) * 100, 4) + "% Invoiced"))

BOOKED1: =IF(AND($Project1 = "-", $Exercise1 = "-"), "-", IF(AND(OR($Project1 = "", $Project1 = "-"), OR($Exercise1 = "", $Exercise1 = "-")), "", IF(OR($Exercise1 = "", $Exercise1 = "-"), SUMIFS(BOOKED$2:BOOKED$344, $Project$2:$Project$344, $Project1), IF(OR($Project1 = "", $Project1 = "-"), SUMIFS(BOOKED$2:BOOKED$344, $Exercise$2:$Exercise$344, $Exercise1), SUMIFS(BOOKED$2:BOOKED$344, $Project$2:$Project$344, $Project1, $Exercise$2:$Exercise$344, $Exercise1)))))

INVOICED1: =IF(AND($Project1 = "-", $Exercise1 = "-"), "-", IF(AND(OR($Project1 = "", $Project1 = "-"), OR($Exercise1 = "", $Exercise1 = "-")), "", IF(OR($Exercise1 = "", $Exercise1 = "-"), SUMIFS(INVOICED$2:INVOICED$344, $Project$2:$Project$344, $Project1), IF(OR($Project1 = "", $Project1 = "-"), SUMIFS(INVOICED$2:INVOICED$344, $Exercise$2:$Exercise$344, $Exercise1), SUMIFS(INVOICED$2:INVOICED$344, $Project$2:$Project$344, $Project1, $Exercise$2:$Exercise$344, $Exercise1)))))

RECEIPTS1: =IF(AND($Project1 = "-", $Exercise1 = "-"), "-", IF(AND(OR($Project1 = "", $Project1 = "-"), OR($Exercise1 = "", $Exercise1 = "-")), "", IF(OR($Exercise1 = "", $Exercise1 = "-"), SUMIFS(RECEIPTS$2:RECEIPTS$344, $Project$2:$Project$344, $Project1), IF(OR($Project1 = "", $Project1 = "-"), SUMIFS(RECEIPTS$2:RECEIPTS$344, $Exercise$2:$Exercise$344, $Exercise1), SUMIFS(RECEIPTS$2:RECEIPTS$344, $Project$2:$Project$344, $Project1, $Exercise$2:$Exercise$344, $Exercise1)))))

COSTS1: =IF(AND($Project1 = "-", $Exercise1 = "-"), "-", IF(AND(OR($Project1 = "", $Project1 = "-"), OR($Exercise1 = "", $Exercise1 = "-")), "", IF(OR($Exercise1 = "", $Exercise1 = "-"), SUMIFS(COSTS$2:COSTS$344, $Project$2:$Project$344, $Project1), IF(OR($Project1 = "", $Project1 = "-"), SUMIFS(COSTS$2:COSTS$344, $Exercise$2:$Exercise$344, $Exercise1), SUMIFS(COSTS$2:COSTS$344, $Project$2:$Project$344, $Project1, $Exercise$2:$Exercise$344, $Exercise1)))))

COMMENTS1**: =IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(OR(INVOICED1 = 0, INVOICED1 = ""), "No Receivables", Nets1 + " Net |  " + LEFT(ROUND((INVOICED1 - COSTS1) / INVOICED1, 3) * 100, 4) + "% Mar | " + LEFT(ROUND(RECEIPTS1 / INVOICED1, 3) * 100, 5) + "% Rcvd" + " | " + Nets2 + " Due"))

** And, for parsing a number to a string, I had to become a hack (as there may very well be a better way.?.) and use a series of hidden columns to do so, as I wanted readable monetary figures to be included in a cells contents as part of a built-up text string, and SS doesn't yet have a TEXT function that I could find (there is some error avoidance herein as well):

cent1: =IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(INT(INVOICED1 - COSTS1) = INVOICED1 - COSTS1, ".00", IF(INT((INVOICED1 - COSTS1) * 10) = (INVOICED1 - COSTS1) * 10, RIGHT(INVOICED1 - COSTS1, 2) + "0", RIGHT(ROUND(INVOICED1 - COSTS1, 2), 3))))

hund1: =IF(AND(Project1 = "-", Exercise1 = "-"), "-", RIGHT(INT((INVOICED1 - COSTS1) / 1), 3))

thous1: =IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(INVOICED1 - COSTS1 >= 1000, RIGHT(INT((INVOICED1 - COSTS1) / 1000), 3), ""))

mils1: =IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(INVOICED1 - COSTS1 >= 1000000, RIGHT(INT((INVOICED1 - COSTS1) / 1000000), 3), ""))

bils1: =IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(INVOICED1 - COSTS1 >= 1000000000, RIGHT(INT((INVOICED1 - COSTS1) / 1000000000), 3), ""))

trils1: =IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(INVOICED1 - COSTS1 >= 1000000000000, RIGHT(INT((INVOICED1 - COSTS1) / 1000000000000), 3), ""))

Nets1: 

=IF(AND(Project1 = "-", Exercise1 = "-"), "-", IF(INVOICED1 - COSTS1 < 1000, "$" + hund1 + cent1, IF(INVOICED1 - COSTS1 < 1000000, "$" + thous1 + "," + hund1 + cent1, IF(INVOICED1 - COSTS1 < 1000000000, "$" + mils1 + "," + thous1 + "," + hund1 + cent1, IF(INVOICED1 - COSTS1 < 1000000000000, "$" + bils1 + "," + mils1 + "," + thous1 + "," + hund1 + cent1, IF(INVOICED1 - COSTS1 < 1000000000000000, "$" + trils1 + "," + bils1 + "," + mils1 + "," + thous1 + "," + hund1 + cent1, "UGH! See Tod"))))))

Oh, one last note, I had to hard-code the row limits in the formulas above (i.e. row2:row344).  This is because of the fact that using this approach, the subtotaled data is in row1 and recursion-avoidance is required.  It is a limitation I've become accustomed to both herein and well as with Excel, when making use of summary rows above columns of relevant data.

I'm sure that some will review this and twitch a bit, declaring it too Rube Goldberg-ish.  I get it.  It works for me (and the non-technical boss).  For those who do endeavor to tackle this, I will try to answer any questions.  No promises on timeliness, however, as I am pretty task saturated.  Good luck.

-t

Any update on this?  Formulas should respect filters.   This is a basic element of Excel's table total and frankly common sense.  To have to write a complex sumifs formula defeats the simplicity of a filter.