How can I sum the value of certain criteria within a date range in a separate smartsheet?

Julia Bailey

I need to calculate the time spent on the team's activities in the past month for my monthly reports, I use a separate Summary Smartsheet to gather all the reporting information. So I am trying to add the Time/Hours spent on each of the Activity items within the last month - making sure that the date calculation doesn't break as the year changes

I have tried this formula: =SUMIFS({Copy of PS Activity - New Range 1}, {Copy of PS Activity - New Range 2}, "Support - remote", {Copy of PS Activity - New Range 4}(MONTH(TODAY()=1,12,MONTH(TODAY())-1),IFERROR((YEAR(@cell),0)=IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())))) - where Range 1 is Time/Hours, Range 2 is Activity and Range 4 is Date of Activity but I am getting unparseable. Where am I going wrong?

Paul Newcome

Previous month would look something like this:

=SUMIFS({Copy of PS Activity - New Range 1}, {Copy of PS Activity - New Range 2}, "Support - remote", {Copy of PS Activity - New Range 4}, 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

Previous month would look something like this:

=SUMIFS({Copy of PS Activity - New Range 1}, {Copy of PS Activity - New Range 2}, "Support - remote", {Copy of PS Activity - New Range 4}, 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()))))

Julia Bailey

That worked perfectly, thanks Paul, a ⭐ as always!

Paul Newcome

Happy to help. 👍️