Counting using child rows

Carroll Wall

I have sheet with several parents and children

Day 1(Rows 1-20)

Time A (rows 3-5)

Time B (Rows 7-9)

Time C (Rows 11-20)

Day 2 (rows 21-48)

Time A (rows 22-27) ETC ETC

Rows could be added to any of the time slots at any time.

I want to keep a real time count of how many are in each time slot

If I want a count t of "Day 1 Time B" I can do a =Count(Primary7:Primary9) .But if another row gets added for that time slot, becoming row 10, the formula will not update AND the formulas for the slots below will no longer be accurate as well.

Is there a way to get around the row range problem for real time counting?

Thank you!

Carroll Wall

Mike, Thanks for replying, I tried to show above but formatting limitations in this box did not make it clear

If I want a widget on a dashboard to show how many rows are under, for example, Day 1 Block 4, I can do

=COUNT([DATE/HOUR BLOCK]294:[DATE/HOUR BLOCK]387)

I need that widget to show real time numbers. However, If a row is added to Day 1 Block 4, the last row in that block becomes 388 and all the other blocks get pushed down one as well. The formula above will not pick up that additional row and formulas in place to report to widgets for the other blocks below it will also become inaccurate.

So my question is how do I count/report/show those numbers accurately as they change over time?

Do I use a different formula type? Can I use a report? Do I have to add a helper column? I experimented with the awesome new report features but the way the sheet owner set up the columns is problematic, I can explain more on that if needed.

Thanks for your attention.

Mike Wilday

Can you add an additional column to your sheet? I would add a formula to the right or left of each parent row that is =Count(Children([Name of Column]@row))

Replace Name of Column with the name of your actual column (It's scratched out) and put that formula next to each block row to count the number of rows beneath the block.

In my example below I used: =COUNT(CHILDREN([Task Name]@row)) next to each block.

You can then use that count to create you widget. You could also join the formula with the Block names to get something like this... ="[ " + COUNT(CHILDREN([Task Name]@row)) + " ] - Block 1 Friday - 7am"

Mike Wilday

Can you share a screenshot so we can have an example of what you are looking for?

Carroll Wall

Mike, Thanks for replying, I tried to show above but formatting limitations in this box did not make it clear

If I want a widget on a dashboard to show how many rows are under, for example, Day 1 Block 4, I can do

=COUNT([DATE/HOUR BLOCK]294:[DATE/HOUR BLOCK]387)

I need that widget to show real time numbers. However, If a row is added to Day 1 Block 4, the last row in that block becomes 388 and all the other blocks get pushed down one as well. The formula above will not pick up that additional row and formulas in place to report to widgets for the other blocks below it will also become inaccurate.

So my question is how do I count/report/show those numbers accurately as they change over time?

Do I use a different formula type? Can I use a report? Do I have to add a helper column? I experimented with the awesome new report features but the way the sheet owner set up the columns is problematic, I can explain more on that if needed.

Thanks for your attention.

Mike Wilday

Can you add an additional column to your sheet? I would add a formula to the right or left of each parent row that is =Count(Children([Name of Column]@row))

Replace Name of Column with the name of your actual column (It's scratched out) and put that formula next to each block row to count the number of rows beneath the block.

In my example below I used: =COUNT(CHILDREN([Task Name]@row)) next to each block.

You can then use that count to create you widget. You could also join the formula with the Block names to get something like this... ="[ " + COUNT(CHILDREN([Task Name]@row)) + " ] - Block 1 Friday - 7am"