Struggling with a SUMIFS date range formula

For this use case, I need to sum the total hours (Range 3) for every entry that has a date between January 1, 2021 and January 31, 2021 (Range 1).

The formula I have seems close but is still coming up as Unparseable.

Ideally, what I want is an evergreen formula that can be applied to every calendar month in the year.

Here is my existing formula. Help please! @Paul Newcome tagging you as always as you are the formula guru!

=SUMIFS({Daily Log Tracker - Template Range 1},{Daily Log Tracker - Template Range 1}, >=1012021, [{Daily Log Tracker - Template Range 1},<=13121], {Daily Log Tracker - Template Range 3})

Kelly L. Gabel

Owner/Founder

Tech-Savvy Academy

kellylgabel.com

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are a couple of issues. The syntax for SUMIFS is opposite that of SUMIF where the range to sum comes first in this one. You will also need to remove the square brackets. And finally dates need to be entered into a formula using a DATE function.

    Last but not least I suggest using MONTH and YEAR functions instead of hardcoding dates in. It allows you to just forget about whether that particular month has 28, 29, 30, or 31 days and lets you just specify year and month.

    DATE(yyyy, mm, dd)


    Assuming Range 1 is the dates and Range 3 is what you want added, give this a try.

    =SUMIFS({Daily Log Tracker - Template Range 3}, {Daily Log Tracker - Template Range 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Hahaha. 🧙

    Happy to help. 👍️


    You are only missing the "range" for the CONTAINS function. In this case it will be an "@cell" reference.

    =COUNTIF({PMO Support Tasks Dept Stakeholders}, CONTAINS(Primary@row, @cell))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There are a couple of issues. The syntax for SUMIFS is opposite that of SUMIF where the range to sum comes first in this one. You will also need to remove the square brackets. And finally dates need to be entered into a formula using a DATE function.

    Last but not least I suggest using MONTH and YEAR functions instead of hardcoding dates in. It allows you to just forget about whether that particular month has 28, 29, 30, or 31 days and lets you just specify year and month.

    DATE(yyyy, mm, dd)


    Assuming Range 1 is the dates and Range 3 is what you want added, give this a try.

    =SUMIFS({Daily Log Tracker - Template Range 3}, {Daily Log Tracker - Template Range 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))

  • Kelly Gabel
    Kelly Gabel ✭✭✭✭✭✭

    @Paul Newcome I have concluded that you are magic :) LOL Thank you!

    I'm going to throw one more at you since I know you are online right now haha.

    I also need a COUNTIF formula for finding each occurrence of a picklist option within a multi-select cell. For example,

    What's happening with my formula is that it only counts it if it is the only value in the cell, not if it is at least 1 of the values in the cell. (does that make any sense at all?)

    What I currently have is: =COUNTIF({PMO Support Tasks Dept Stakeholders}, CONTAINS(Primary@row))

    where the range is a column on another sheet, and I'm looking for the primary value on my calculations sheet.

    This is one of many formulas I've tried and is coming up as Incorrect Argument...

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Hahaha. 🧙

    Happy to help. 👍️


    You are only missing the "range" for the CONTAINS function. In this case it will be an "@cell" reference.

    =COUNTIF({PMO Support Tasks Dept Stakeholders}, CONTAINS(Primary@row, @cell))

  • Kelly Gabel
    Kelly Gabel ✭✭✭✭✭✭

    That was it! Thanks so much!

    Kelly L. Gabel

    Owner/Founder

    Tech-Savvy Academy

    kellylgabel.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!