Need Help with Formula for Rolling Metrics

Vivien Chong

I am working on a 6 months rolling metrics calculation for a point system.

A simple data involved will be as follows:

1) Date column

2) Points column (0-10)

3) Name column

I need to calculate a 6 months rolling sum of points, for each person.

I couldn't figure out how to calculate the sum of points for each person in 6 months rolling?

(When in the month of August, it will be a sum from March - August, when in the month of September, it will be a sum from April - September).

Anybody can advice on this?

Find more posts tagged with

Formulas

Comments

Paul Newcome

EDITED to account for the rest of the criteria. Initial formula only looked at dates.

Try something along the lines of this...

=SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), [Name Column]:[Name Column], "John Smith")

Vivien Chong

Thanks Paul. Looking at your formula, I really couldn't understand it. . But I'll give it a try and try to understand how it works.

Appreciate your feedback.

Paul Newcome

Happy to help!

I was running short on time, otherwise I would have been more descriptive in the original post, but here it is (better late than never).

So the easy part is knowing that we need a SUMIFS because we are wanting to sum a data set based off of multiple sets of criteria (date and name).

We know that the syntax requires our sum range first, so we start with

=SUMIFS([Points Column]:[Points Column],

Next we need to establish our first criteria range. In this formula, I started with the dates.

=SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column],

Now we establish our date criteria (this is the tricky part, but I'll break it down step by step).

We know we have two sets of criteria for the same range, so we use an AND statement.

AND(logical statement 1, logical statement 2)

We know that the high end of our range will be TODAY(), but that would require opening the sheet daily to activate the TODAY() function just to capture any new entries (if any).

So how can we just go ahead and capture the entire current month? Easy right?

MONTH(TODAY())

Except if you have data across multiple years, you're going to be including all of that. We have two options on how to account for this. Honestly... now that I am spelling it out, I realize I chose the harder of the two. Lol.

But it maintains a consistent format for both sets of criteria so we'll stick with it.

Since the number of days in the current month changes with each month, we don't want to have to manually update the formula every single month and hope we didn't accidentally mess something up. So the easiest way to do it is to say we want to look at everything that is less than or equal to the day before the first of next month.

This will account for 28, 29, 30, or 30 days without us having to regularly edit anything.

So the first of next month would look something like this...

DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)

That's all fine and dandy until we hit December. There is no 13th month. So what do we do? We use an IFERROR to establish that.

IFERROR(the normal function to run, what to do in the event of an error)

IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), what to do in the event of an error)

That first part is where we put our first DATE function. Simply drop it in, and that part is done.

So if the month is December and we want to go to January 1 of next year, we would say this...

DATE(YEAR(TODAY()) + 1, 1, 1)

This gives us Jan 1 of next year, so we will just drop that into the second portion of our IFERROR.

IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1))

Now we have the first of next month established. Subtracting 1 from that will give us the last day of the current month.

IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Now we have our high range and can use an @cell reference to turn it into a logical statement then drop that into the first portion of our AND statement.

@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, logical statement 2)

On to the low end of our range...

We want to go 6 months in the past. Again... because of the differing number of days in each month, this exact number will vary, so the most consistent way to automate this is to just subtract 6 months and use the first day of whatever that month is.

DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1)

But what if this month is March? There is no month of negative 3, so we use another IFERROR statement. But how do we go back a year and establish which month?

Well let's break this step down just a little further...

We want a result of -6 months.

If we subtract 1 year (12 months) then add 6 months, that will do the trick.

So we say

DATE(YEAR(TODAY()) - 1,

to subtract the 12 months.

DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)

to add six months and bring us back up to -6 months. Use 1 for the day, string it all together the same way we did the first IFERROR

IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))

turn it into a logical statement

@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))

and drop that into the second portion of our AND statement.

AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, logical statement 2)

AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)))

So now lets roll on back to our SUMIFS.

We have established our sum range, our first criteria range and our first criteria set.

All that is left now is adding in the name using the same syntax as above:

criteria range, criteria

[Name Column]:[Name Column], "John Smith"

Since that's our last set of criteria, we can throw it in and close out the SUMIFS...

And there you have it. The breakdown on how to use an AND statement to combine multiple sets of criteria for the same range within a SUMIFS (also works in a COUNTIFS) as well as a step by step walk through of how to work with months using an IFERROR statement.

Does this breakdown work? I'd be more than happy to explain further if you'd like to ask any questions or anything.

Vivien Chong

Hi Paul

The formula works, and I was trying to figure out every part of it and was about to ask you if you don't mind to explain further. But you shared on how you derived at the formula before I even asked.

I will read through and try to understand what does each part of the formula means.

Truly appreciate your assistance and help.

Paul Newcome

Happy to help!

Vivien Chong

Hi Paul

I was working on this formula currently and would like to have the AVG instead of SUM for certain items.

I thought I can replace the "SUM" with "AVG", but it doesn't work.

Do you have any idea, how I can get the average instead of sum?

Thanks Paul.

Paul Newcome

We are actually using a SUMIFS. Note the S on the end. That is going to be the biggest factor as to how we go about finding the average.

If it was just a simple SUM, we could replace it with AVG. If it was a SUMIF, we could replace it with an AVERAGEIF. But alas... There is no AVERAGEIFS (I've already requested this feature, but it wouldn't hurt if you also submitted a Product Enhancement Request for it as well). Instead we will need to use an AVG/COLLECT. Fortunately that ends up having the same syntax as a SUMIFS, so there is very little to change.

Just replace SUMIFS with AVG(COLLECT( and throw an extra closing parenthesis on the end of the formula. This will give you the average of the same exact numbers you are currently summing.

Vivien Chong

Thanks Paul.

Alright I will also submit a request for the Product Enhancement.

You are a great help!

Have a blessed day!

Paul Newcome

Happy to help!

Tyrone T

https://community.smartsheet.com/discussion/comment/194441#Comment_194441

Would this solution work with the COUNTIFS() function in place of the SUMIFS()?

Tina Davis

Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a loss to get it to work for 12 months in the past. Below is my current formula and would appreciate any help.

=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

Sign in to join the conversation!

Quick Links

Trending discussions

Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…
How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Find information within a formula
Hello, I am trying to find information within formulas that reference a given cell using CTRL + F. The data is in the cell [2028 (CY)]214. I have tried the following: [2028 (CY)]214, *[2028 (CY)]214, = [2028 (CY)]214, = * [2028 (CY)]214. However, it returns 'no matches found'. Does anyone have advice or is this not…