Sum total minutes at a particular data center over the last twelve full months

Kelly McLaughlin

Hi,

Been trying to figure this out for awhile. Here's my issue:

I am trying to create a rolling sum of the minutes that there is an outage at a particular data center over the last 12 complete months (not including the current month) by referencing another sheet in Smartsheet. We eventually want this number to update automatically for the last twelve months, but the sheet I'm referencing's dates don't go back that far.

One of the formulas I tried was

=SUM({Uptime Minutes}, {Event Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1), {Event Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 2))... etc. for the last twelve months, but I wasn't able to add criteria to reference which Data Center I wanted.

I have tried SUMIF/SUMIFS, as well, but because the data doesn't quite reach 12 months ago, the value returns a zero (an error).

Another formula I have that works similarly is

=SUMIFS({Uptime Minutes}, {Event Date}, AND(IFERROR(MONTH(@cell), 0) = (MONTH(TODAY() - DAY(TODAY())))), {Data Center}, FIND("Data Center Name", UPPER(@cell)) > 0)

which returns the Uptime Minutes for the full previous month at a named Data Center. Is it possible to do this for the last twelve months, and if a particular month doesn't exist in my referenced sheet, ignore the command?

I have thought about trying something like FIND(the most historic date in the column) IF(MONTH [is less than] MONTH(TODAY()) - 12),

or maybe

=SUMIF({Uptime Minutes}, {Data Center}, FIND("Data Center Name", UPPER(@cell)) > 0, IF(DATE [is less than] MONTH(TODAY())-1, AND(DATE [is greater than] MONTH(TODAY())-13)))

but I'm not quite sure how to do this/if it's possible.

Would love some advice! Thanks!

Genevieve P.

Hi @Kelly McLaughlin

This will look a little complicated, so I'll break down each section for you. Where it gets tricky is when we cross over into a different year, since MONTH - 13 won't get you the correct month, but will instead return a negative number.

First criteria:

You can keep the first bit exactly the same as your last formula example.

=SUMIFS({Uptime Minutes}, {Data Center}, FIND("Data Center Name", UPPER(@cell)) > 0, 

This year's month criteria:

Then we need to add in the other criteria. We need to look for if the Year is the same as Today's year, then all the Months in the past.

So first we do the range, then use YEAR to find the same YEAR. Then we list the range again, and use MONTH to find the months that are less than Today's month.

{Event Date}, (IFERROR(YEAR(@cell), 0) = (YEAR(TODAY()))), {Event Date}, (IFERROR(MONTH(@cell), 0) < (MONTH(TODAY()))))

Last year's months - first criteria:

Now, this only looks in the past for this current year. To add in last year's months (but only the relevant ones) you'll want to add another SUMIFS formula to this first one, using a + symbol.

+ SUMIFS({Uptime Minutes}, {Data Center}, FIND("Data Center Name", UPPER(@cell)) > 0, 

Last year's months - date criteria:

And then we do the same thing, but instead of searching for TODAY's Year, we search for Today's Year - 1. Then instead of a date being less than Today's Month, we want to find all the months that are equal to or greater than Today's Month (but in last year)

{Event Date}, (IFERROR(YEAR(@cell), 0) = (YEAR(TODAY()) - 1)), {Event Date}, (IFERROR(MONTH(@cell), 0) >= (MONTH(TODAY()))))

FULL FORMULA:

=SUMIFS({Uptime Minutes}, {Data Center}, FIND("Data Center Name", UPPER(@cell)) > 0, {Event Date}, (IFERROR(YEAR(@cell), 0) = (YEAR(TODAY()))), {Event Date}, (IFERROR(MONTH(@cell), 0) < (MONTH(TODAY())))) + SUMIFS({Uptime Minutes}, {Data Center}, FIND("Data Center Name", UPPER(@cell)) > 0, , {Event Date}, (IFERROR(YEAR(@cell), 0) = (YEAR(TODAY()) - 1)), {Event Date}, (IFERROR(MONTH(@cell), 0) >= (MONTH(TODAY()))))

Let me know if this works for you!

Cheers,

Genevieve