2 Replies Latest reply on Jun 1, 2012 8:50 AM by Dellalitam

    Filtering and summary rows


      is there a way to filter the form file and responses, then as you filter by date- have the summary row calculate based on the filter?


      In other words if I want the summary row to calculate only the submissions for March, how do i do that.

        • 1. Re: Filtering and summary rows
          Todd Rein Level 3



          There's no built in way to do this.  However with a bit of formula magic, you can probably do this.  To explain how, I'm going to make the following assumptions:

             - The column you are performing the summary formula on is in column B

             - The summary calculation you want is a sum of all the values in column B

             - Your time submitted column is column A


          In FormsCentral, like Excel, we have a SUMIF formula function.  That function will add all the cell values that meet a certain criteria.  The syntax for the SUMIF function is

             - SUMIF(range, criteria, [sum_range])


          In this example, we want to sum all cells in column B, who's submission was in March (and we'll assume March or after, instead of just March).  So we'll have:

            - range - this is the range of cells we'll test our criteria to indicate if we want to add the cell or not, so the range will be column A, the time submitted column, because we will test if it was submitted in or after March.  We can refer to an entire column of cells in several ways, but usually I find it simplist to use @the column letter, so in this case it will be @a.


            - criteria - this will be a partial formula, that will be tested against each cell.  Because it's a partial formula, inside of another formula, it's going to look a bit wierd, with all the quotes needed.  See below in the final formula.  More importantly, what it will look like is a test of the submitted value, to see if it's in or after March.


          - sum_range - in this case, we've said the value we want to add is in column B, so it will @B


          Here's the final formula:

             = sumif(@A, ">=datevalue(""03/01/2012 "")", @B)


          There's also countif, and averageif, you need counts or averages.


          Lastly, if this doesn't work for your needs, I suggest posting this to the ideas section.  We look at that to help guide us as to what new features to add in the future.


          Hope this helps,


          • 2. Re: Filtering and summary rows
            Dellalitam Level 1

            Thanks Todd, ill try it