Count filtered rows dinamically RRS feed

  • Question

  • Hello

    I was wondering if any of you have an idea on how to customize a formula field that will count the number of tasks in each Summary task but only for the rows shown when filtered.

    For example, let's say that i have a summary with 10 task, and 3 of them are completed, and i filter to see Incomplete tasks.

    Then i would like this field to count 7 but on the ways that i tried it always counts all tasks, even those who are hidden.

    Is there any way to do that?

    Is there a MS field saying if the task is shown or hidden?

    Just to be clear, i want it to count the shown tasks for every filter i use, not the incomplete tasks.  i might use several of filters and need to count each time.


    Ofir Marco , MCTS P.Z. Projects

    Tuesday, September 8, 2015 5:08 AM


All replies

  • Yes, there is a way...

    - Insert Number1 field to the table (better to make it the first column after the indicators if it is the Entry table), set its value to 1 by entering 1 to the Edit formula box, and then set the roll-up option for the summary rows to <Count All>.

    - Apply any filter you want, then click AutoFilter for Number1 and select <Group on this field>. Number1's cell at the group summary row will show the total number of the group rows displayed which is the value that you want. 

    The trick here is that the group summary rows roll up only the data of the group rows displayed. 

    Tuesday, September 8, 2015 8:00 AM
  • Tuesday, September 8, 2015 10:18 AM
  • Thanks.

    It's a great idea.

    User then tried to make my life harder saying she also wants the summaries to be assigned with "Yes" and get filtered, but i am working with that idea to align it for that request.

    Ofir Marco , MCTS P.Z. Projects

    Tuesday, September 8, 2015 12:46 PM