SUMIF Previous Month, Current Year

James Caison

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!

Paul Newcome

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

Paul Newcome

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

James Caison

That worked perfectly Paul! I appreciate your time.

Paul Newcome

Happy to help. 👍️

James Caison

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

Nate H

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

Paul Newcome

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

Nate H

Thanks, sir. @Paul Newcome

Paul Newcome

@Nate H Happy to help. 👍️