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.

Best Answer

Answers

  • Paul Newcome
    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))

  • 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))

  • 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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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))

  • Paul


    Thank you so much!

  • Paul Newcome
    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.

  • 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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!