Struggling with a SUMIFS date range formula

Kelly Gabel

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

Paul Newcome

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

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

Paul Newcome

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

@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...

Paul Newcome

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

That was it! Thanks so much!

Paul Newcome

Sure thing! Happy to help. 👍️