SUMIF and AVG or AVERAGEIF

Tommy Chitry

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)))

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

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