# SUMIF Previous Month, Current Year

Hello,

I am trying to use the Sheet Summary feature to calculate [Hours worked] for the previous month based on a date column [Service End Date]. I have the following formula which is giving me a much larger value than expected and I believe this is due to having multiple years of data and only looking at the month it is pulling data from the previous month of several years. I have not been able to figure out how to add a criteria for current year.

=SUMIF([Service End Date]:[Service End Date], MONTH(@cell) = MONTH(TODAY() - 1), [Hours worked]:[Hours worked])

I have read several other posts and solutions but am not having any luck. Thanks in advance!

• ✭✭✭✭✭✭

Try this...

=SUMIFS([Hours worked]:[Hours worked], [Service End Date]:[Service End Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

• ✭✭✭✭✭✭

Try this...

=SUMIFS([Hours worked]:[Hours worked], [Service End Date]:[Service End Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

• That worked perfectly Paul! I appreciate your time.

• ✭✭✭✭✭✭

Happy to help. 👍️

• I modified this formula to look forward a few months a but once we get into the next year (2022) the result is 0. I've tried several ways but get the same 0 result. What am I missing. Thanks in advance

!

=SUMIFS([Man Days]:[Man Days], [Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) + 2), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))) * 8

• ✭✭✭✭✭
edited 11/08/22

Hey, @Paul Newcome, is there a way to add another provision to your formula here? I'm wanting to only sum the values from last month where "Northeast" is in the column labeled "Region." I've tried adding another AND formula but my syntax must be off.

=SUMIFS([Referred Out]:[Referred Out], [Date Only]:[Date Only], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()), AND(Region = "Northeast"))))

• ✭✭✭✭✭✭

@Nate H You would need to continue with the appropriate syntax of range comma criteria.

=SUMIFS([Referred Out]:[Referred Out], [Date Only]:[Date Only], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))), Region:Region, @cell = "Northeast")

• ✭✭✭✭✭

Thanks, sir. @Paul Newcome

• ✭✭✭✭✭✭

@Nate H Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!