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:

Minutes

Names

Task

Date

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!

Tags:

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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!