Count Number of Tasks

eahills

Hi, I am trying to build some dashboards widgets that sum up the number of tasks in a list. The list includes multiple "parent" type rows, but I only want to count the child fields. I created a 'heirarchy' class, but the problem is I need to sum both the 1s and 2s based the various categories.

Also - how do I actually get the dashboard widget to display the # in a text type field?

Victoria_Indimar

Hello @eahills! Instead of doing the hierarchy/parent count, I've done a helper column for child count. Then I count the tasks where children = 0 to get my true Task Count. Hope this helps!

eahills

@Victoria_Indimar -- thanks -- what is the formula to create a child count?

Victoria_Indimar

=count(children([task name]@row))

eahills

@Victoria_Indimar - awesome. Super helpful, done. And how do I roll the sum of the children into a metric widget in a dashboard?

Victoria_Indimar

To add to a dashboard, I typically create a separate summary sheet that rolls up the information you want to generate a chart/graph from.

For example, I see you have Milestones to organize your tasks. Maybe if you have something like Status or % Complete, you can create a little table using formulas to count the # of tasks that meet the criteria.

Then from the Dashboard chart widget, you would select the table from the summary sheet.

eahills

Ok. Interesting @Victoria_Indimar...someone who had created this widget previously referenced a sheet summary data field from my primary sheet....what exactly does this do?

Victoria_Indimar

Ah! Yeah, you could do that as well. It'd be the same formulas you used for each field you have there in the sheet summary. I like to use a separate sheet just to help organize, add more as more metrics are needed/requested, and quickly see how the data rolls-up.

eahills

Curious where these calculations are even located for the Sheet Summary Data...?

Victoria_Indimar

Sheet Summary Data comes from the selected sheet's Summary, available on the right vertical menu.

You'd create a field and enter the formula there, for example:

NishaTKD

@Victoria_Indimar Could you please guide me how I can achieve similar to mentioned in this thread.

I have a sheet with a column that has list of Goals and subgoals. There is another column with status. I would like to create a summary sheet that automatically counts goals and sub goals by status: Here is the screenshot of what I am envisioning:

Ultimately I want to create metric widget on dashboard to display data in following format:

Please guide me what functions/formula I could used to achieve desired view of data. Thanks in advance.

Victoria_Indimar

Hi @NishaTKD !

Do you have something in your master/source sheet that identifies an item as a Goal or Sub-Goal?

If you have a Type defined for it, your Summary sheet would use a COUNTIFS formula such as:

=COUNTIFS({Source Sheet_Type},="Goal", {Source Sheet_Status},="Complete")

which would be updated to the different statuses per column.

Please let me know if you need additional assistance.