Need Help with Formula for Rolling Metrics

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

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

 

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

.

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.

 

=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))​​​​​​​)

.

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...

 

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

.

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.

Happy to help! 

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.

Happy to help! 

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)