Formula help with multiple variables

Dina Miller

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!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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.

All comments

Paul Newcome

Are you wanting to just show basic numbers on your dashboard, or do you want graphs of some sort?

Dina Miller

I would like to use bar charts to report the team's productivity.

Paul Newcome

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.

Dina Miller

Thank you for your help!

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.