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


Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • @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
    Mark Cronk ✭✭✭✭✭✭

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!