Formula help with multiple variables

I would like to use a different sheet to aggregate data for the purposes of creating a dashboard.

I am trying to determine the number of minutes a person spends on a specific task for a specific month.

For example, I would like to know how many minutes Jane spent on emails in the month of January.

Sheet column headers are:





I am currently using the formula listed below but I would like to use the formula on a separate sheet. And I find this formula a little clunky to work with because I have 9 different tasks I am tracking metrics for - (i.e. email, phone calls, admin work) and 5 different people so it is very cumbersome to update every month.

=SUMIFS(Minutes:Minutes, Names:Names, ="Jane", Task:Task, "Emails", Date:Date, MONTH(@cell) = 1)

Any help would be greatly appreciated!


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest a helper column in the source sheet with a formula to check a box if that entry is for the current month.

    =IF(AND(MONTH(Date@row) = MONTH(TODAY()), YEAR(Date@row) = YEAR(TODAY())), 1)

    Then create a separate report for each task and group by person. You can create a summary field in the report to automatically sum up the hours and then use this report to populate your chart.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!