Dynamically Calculate Child Counts Based Upon Filters.

Hi All,

How do I calculate the count of children in a parent row and have it dynamically change depending upon filtered results.

I’ve tried COUNTIF and COUNT plus SUM CHILDREN and they all work showing the correct number of children before a filter is applied.

When I apply a filter, the number of child rows reduce, based upon the filtered criteria, which is correct. But the child COUNT in the PARENT row still totals the same as before the applied filter.

I want the COUNT to summarise the number of rows based upon the filter.

Thank you.

Find more posts tagged with

Sheets

Formulas

Comments

Shaine Greenwood

Hi Scott,

There isn't a way for formula cell references to change dynamically based on an applied filter but I've added your vote for this to our enhancement request list for further consideration.

One potential workaround would be to use cell linking to bring the data you want to calculate on over to another sheet, then create your formula over the cell linked data.

Scott Lynch

Hi Shanie,

Thanks for the reply,

This is disappointing. Simple dynamic counts in a parent cell that can automatically recalculate when the child rows are filtered is a glaring omission on the filtered data feature. Thank you anyway.

Taleb Alkhaja

I agree with Scott - this is desperately needed. For my case in particular, I am using Smartsheet for budget planning and have high and low priority Child rows - I need the Parent cell to recalculate if I filter out the low priority items, for example.

David K

It seems your response addresses the inverse of his question (and mine). He is seeking a way to dynamically calculate information that has been filtered. Currently my parent row calculations include all data even though I have filtered the data. It is truly hard to imagine that calculations cannot function as a result of the filtered data? Your response seems to reflect the desire to calculate data that was excluded from the filter, which wasn't the question (unless I missed it, in which case please address my concern). Thanks,

Capture 2.PNG

Shaine Greenwood

Apologies David, I can see how my response could easily be misconstrued. I've edited it to hopefully make it more cohesive.

There isn't a way to have formula calculations dynamically reference cells based on an applied filter.

They'll reference the same set of cells and return the same value regardless of whether a filter is applied to the cells the formula references.

Scott Lynch

Hi Shaine,

This issue really became far too much of a stumbling block for me, and basically made the filter feature pretty pointless.

I have since moved many of our more detailed projects from Smartsheet over into FileMaker.

In FileMaker "Summary Fields" can dynamically calculate results based upon filter criteria. This has made filtering and reporting far more intuitive and much easier to complete and print.

Regards

ggoicuria

Has there been a solution for this since this was posted?

Thanks

Richard

Agreed, it does seem to be logical that the filter applies across the whole view.