Count number of tasks by day to know how many workers are needed

StefanH

Hi Everyone,

Brand new to smartsheet and I have very little understanding of it's capabilities. I've built an extensive gantt chart with tasks going out over a year. When I manually count the tasks, I can see that I need anywhere between 2 and 5 workers on a given day based on the number of active tasks. As I continue to build out this project plan, manually counting will become impractical. Is there an easy way to have smartsheet show me a count of tasks by day?

My example is below. Is there a way to add a summary row at the bottom or top that counts tasks like I've shown at the top in red text? This would allow me to quickly see staffing needs by day/week.

Paul Newcome

Yes. You would use (for example) a checkbox column with a column formula of

=IF(COUNT(CHILDREN([Primary Column Name]@row)) = 0, 1)

Then you would include another range/criteria set in the COUNTIFS to count only rows that meet the date criteria and have that box checked.

{Checkbox Range}, @cell = 1

Paul Newcome

Technically we could get something pretty close to that, but it will not scale very well at all. My recommendation would be to calculate this on another sheet and then potentially display it on a dashboard in a chart.

The separate sheet would have a date type column with every date for the project listed on their own individual rows. There are ways to automate this to allow for additional scaling and flexibility. If needed, we can explore those options later. For now let's at least get the counts on the sheet.

Using a COUNTIFS with {Cross Sheet References}, you would end up with somethign along the lines of

=COUNTIFS({Project Plan End Date Column}, @cell >= Dates@row, [Project Plan Start Date Column}, @cell <= Dates@row)

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

StefanH

@Paul Newcome A separate cross reference sheet would be a perfectly acceptable solution. Thanks!

I've figured out how to create a new sheet, and also how to reference the other sheet for the end date column and start date column in the formula that you gave, but I'm not sure what to do with the @cell and dates@row parts. When I put those in as shown, I get #unparseable, so I assume I'm missing something I should be referencing there.

Any chance you could dumb it down a bit more? :)

=COUNTIFS({Project Plan End Date Column}, @cell >= Dates@row, [Project Plan Start Date Column}, @cell <= Dates@row)

Paul Newcome

@cell should stay as is. Dates@row is a reference to the [Dates] column in the sheet containing the formula. I do notice that your second cross sheet reference has an opening square bracket instead of an opening curly bracket. That is most likely where the error is coming from.

StefanH

Getting closer I think. I'm getting values now, but I think it's not counting the tasks as expected.

For the dates below, I know there are only 2 tasks on those given dates, but it's calculating between 19 and 21. Any ideas?

Paul Newcome

I'd have to see what your ranges are covering. First range should be the end dates and second range should be the start dates.

StefanH

Range 1

Range 2

Paul Newcome

What happens if you apply a filter to the source sheet that mimics the COUNTIFS for lets just say 1 October 2024?

StefanH

I put in this filter

it returned these rows, even though I would have only wanted it to return the one's with tasks that day (top 2)

I'm wondering if we should be counting only child tasks and ignoring the main task? Is there a way to do that?

Paul Newcome

Yes. You would use (for example) a checkbox column with a column formula of

=IF(COUNT(CHILDREN([Primary Column Name]@row)) = 0, 1)

Then you would include another range/criteria set in the COUNTIFS to count only rows that meet the date criteria and have that box checked.

{Checkbox Range}, @cell = 1

StefanH

Thank you! This worked great!