Count "Assigned to" and Completed Year from Multiple sheets


I have 15 sheets with an "Assigned To" column. When the task it completed the task moves to an Archive sheet (there are 15 Archive sheets also) I need to count the number of completed tasks for each "assigned To" person and the year that it was completed. I have added a Date Modified column to the Archive sheet. Below is a snap shot of the matrix that I am tracking the number of open and completed tasks on. But now was requested to track the year completed.


  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    Is the question why isn't my formula working?

    If so, you need to use the following Syntax:

    =COUNTIF({Sum Range}, =[Assigned To]@row)+..........

    Your currently using COUNTIFS not COUNTIF. Since there in only 1 criteria COUNTIF is what you want. But COUNTIFS has a different syntax.

  • gdooley@jbelectric

    My formula is working. I just now want to count the completed items for the year 2023. Below is a snap shot of one of the sheets that I am gathering my information from, the snap shot above is the matrix that the information that I am counting filters into.

    This will be the same for the other 14 sheets. When a job is completed and closed it moves to an archive sheet where I pull the information from. (snap shot below) My question is how do I modify the formula above to include the year and only count the ones with that year specified.

  • gdooley@jbelectric

    This is the formula that I modified to count only the jobs the "Assigned To" person closed in 2022.

    =COUNTIFS({Archive DMC - Assigned To/Foremen}, =[Assigned To/Formen]@row), (AND({Archive DMC - Date Closed},ISDATE(2022)

    This is not working.

    Is it because the Date column properties are set up to auto populate with date Modified? Do I need to manually put a date in that cell in order for it to see it?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!