SUMIF and AVG or AVERAGEIF

Hello,

I'm trying to use two different formulas that looks at multiple Criteria to return an Average Daily number of hours spend on tasks within a category and the other to return the total number of hours in the week spent on specific tasks. With only a single criteria I can get each to work, but I'm struggling with the multiple criteria. Any help would be greatly appreciated!

For the Daily Average I'm using:

=AVG(COLLECT({Duration}, {Category}, Category@row, [{ARML}], ["Tommy_Chitry"], {Duration}))

For the Weekly Total I'm using:

=SUMIFS({Duration}, {Category}, Category@row, [{ARML}], ["Tommy_Chitry"], {Year}, YEAR(TODAY()), {Weeknumber}, IF(WEEKNUMBER(TODAY()) - 1 = 0, 52, WEEKNUMBER(TODAY() - 1)))

Find more posts tagged with

Accepted answers

Mark Cronk

Good morning,

Try these. The HAS function looks for an exact match. You may need to adjust the value to get the right response:

=AVG(COLLECT({Duration}, {Category}, Category@row, {ARML}, HAS(@cell, "Tommy_Chitry")

=SUMIFS({Duration}, {Category}, Category@row, {ARML}, HAS(@cell,"Tommy_Chitry"), {Year}, YEAR(TODAY()), {Weeknumber}, IF(WEEKNUMBER(TODAY()) - 1 = 0, 52, WEEKNUMBER(TODAY() - 1)))

Work?

Mark

All comments

Mark Cronk

Good morning,

Try these. The HAS function looks for an exact match. You may need to adjust the value to get the right response:

=AVG(COLLECT({Duration}, {Category}, Category@row, {ARML}, HAS(@cell, "Tommy_Chitry")

=SUMIFS({Duration}, {Category}, Category@row, {ARML}, HAS(@cell,"Tommy_Chitry"), {Year}, YEAR(TODAY()), {Weeknumber}, IF(WEEKNUMBER(TODAY()) - 1 = 0, 52, WEEKNUMBER(TODAY() - 1)))

Work?

Mark

Tommy Chitry

@Mark Cronk Yes sir that did the trick. I just had to add an IFERROR on the front so that when it had no data it displayed 0. Thanks so much for the help!

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark

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