Count employee (resource) tasks from second smartsheet if the tasks fall in a date range

I am trying to count the how many tasks a resource is assigned in a project schedule (sheet 1) if they are in a date range (sheet 1). The calculations is being done in second sheet (sheet 2).

Example: Peter (resource field is sheet 1) has 10 tasks in a Planned Start date ( Planned start date field in sheet 1). I'd like to see the tasks from 3/18/24 through 3/23/24.

I tried the following formulas.

Attempt 1:

=COUNTIFS({Assign To}, Teammates@row, True([Planned Start Date]@row >= DATE(2024, 3, 18), [Planned Finished Date]@row <= DATE(2024, 3, 23))) - Error unparsehable.

Attempt 2:

I simply and add two fields to the current sheet to eliminate using two sheets until I figure this out.

=COUNTIFS({Assign To}, Teammates@row, ([Planned Start Date]@row “>=DATE(2024, 3, 18)” AND “<= DATE(2024, 3, 23)”) = Error unparsehable.

Attempt:

=COUNTIFS({Assign To}, Teammates@row, AND([Planned Start Date]@row >= DATE(2024, 3, 18), [Planned Finished Date]@row <= DATE(2024, 3, 23))) - Invalid Operator

I know the count for teammates work. I just can't figure out the date range piece.

Any help would be appreciated!

Thank you.

Find more posts tagged with

Accepted answers

Paul Newcome

Are you getting an error message when you use the below?

=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Start Date}, <= DATE(2024, 3, 23))

All comments

Paul Newcome

You would use something more along the lines of:

=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Finish Date}, <= DATE(2024, 3, 23))

Mar.Max

Paul

Thank you for answer.

To give more information. The goal is to show the amount of tasks that started by using "Planned Start date" in a week. There will be a second field, with a separate formula, showing "Actual start date."

So using only "planned start date", show me the count for employee Bob tasks that started for the week of 3/18/24. Then I have a second field that is counting Bob tasks that were actual started the week of 3/18/24.

These two formulas will be done for each week of a given month.

Right now, I am working on the planned date calculation figuring I can used the same formula for Actual start date.

Is there a way to combine a date range for the planned start date field, or do I have to list the date field twice?

Below formula, I tried to repeat the Start Date, but Smartsheet didn't like this.

=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Start Date}, <= DATE(2024, 3, 23))

Mar.Max

I tried this as well, but Smartsheet doesn't seem to like the "and" operator. Error = Invalid Operator

=IF([Planned Start Date]@row >= DATE(2024, 3, 18), IF(AND([Planned Start Date]@row <= DATE(2024, 3, 23), COUNTIFS({ Assign To}, Teammates@row))))

Paul Newcome

Are you getting an error message when you use the below?

=COUNTIFS({Assign To}, Teammates@row, {Start Date}, >= DATE(2024, 3, 18), {Start Date}, <= DATE(2024, 3, 23))

Mar.Max

Paul

Thank you so much!

Paul Newcome

Is it working now? All I did was copy/paste one of your formulas that you wrote based on the syntax I had provided.

Mar.Max

I missed a comma in my formula. Reading your formula, helped me step back, and made it very clear.

Final formula I used:

=COUNTIFS({Assign To}, Teammates@row, {Planned start 3 11-15 2024}, >=DATE(2024, 3, 18), {Planned start 3 11-15 2024}, <=DATE(2024, 3, 23))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.