Need Help with Formula for Rolling Metrics
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?
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).
=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@cellreference 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.
-
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 ✭✭✭✭✭✭
-
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.
如果这只是一个简单的总和,我们可以替换它ith 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 aProduct Enhancement Requestfor 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.
只是条件求和换成AVG(收集(和抛出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 ✭✭✭✭✭✭
-
Tyrone T ✭Happy to help!![\"yes\"](\"\/libraries\/smiley\/images\/thumbs_up.png\" "\\"yes\\"")
<\/img><\/p>\n<\/p>\n
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).<\/p>\n
<\/p>\n
=SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell<\/a> <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell<\/a> >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), [Name Column]:[Name Column], \"John Smith\")<\/p>\n
.<\/p>\n
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).<\/p>\n
We know that the syntax requires our sum range first, so we start with<\/p>\n
<\/p>\n
=SUMIFS([Points Column]:[Points Column],<\/strong><\/p>\n
.<\/p>\n
Next we need to establish our first criteria range. In this formula, I started with the dates.<\/p>\n
<\/p>\n
=SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], <\/strong><\/p>\n
.<\/p>\n
Now we establish our date criteria (this is the tricky part, but I'll break it down step by step).<\/p>\n
We know we have two sets of criteria for the same range, so we use an AND statement.<\/p>\n
<\/p>\n
AND(<\/strong>logical statement 1, logical statement 2)<\/strong><\/p>\n
.<\/p>\n
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).<\/p>\n
<\/p>\n
So how can we just go ahead and capture the entire current month? Easy right?<\/p>\n
<\/p>\n
MONTH(TODAY())<\/p>\n
<\/p>\n
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.<\/p>\n
<\/p>\n
But it maintains a consistent format for both sets of criteria so we'll stick with it.<\/p>\n
.<\/p>\n
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.<\/p>\n
<\/p>\n
This will account for 28, 29, 30, or 30 days without us having to regularly edit anything.<\/p>\n
.<\/p>\n
So the first of next month would look something like this...<\/p>\n
<\/p>\n
DATE(YEAR(TODAY()), MONTH(TODAY()) + 1<\/strong>, 1)<\/p>\n
.<\/p>\n
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.<\/p>\n
<\/p>\n
IFERROR(the normal function to run, what to do in the event of an error)<\/p>\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)<\/u>, what to do in the event of an error)<\/p>\n
.<\/p>\n
That first part is where we put our first DATE function. Simply drop it in, and that part is done. <\/p>\n
<\/p>\n
So if the month is December and we want to go to January 1 of next year, we would say this...<\/p>\n
<\/p>\n
DATE(YEAR(TODAY()) + 1<\/strong>, 1, 1)<\/p>\n
<\/p>\n
This gives us Jan 1 of next year, so we will just drop that into the second portion of our IFERROR.<\/p>\n
.<\/p>\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)<\/u>)<\/p>\n
.<\/p>\n
Now we have the first of next month established. Subtracting 1 from that will give us the last day of the current month.<\/p>\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1<\/strong><\/p>\n
.<\/p>\n
Now we have our high range and can use an @cell<\/a> reference to turn it into a logical statement then drop that into the first portion of our AND statement.<\/p>\n
@cell<\/a> <= <\/strong>IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1<\/p>\n
<\/p>\n
AND(@cell<\/a> <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1<\/u>, logical statement 2)<\/p>\n
.<\/p>\n
On to the low end of our range...<\/p>\n
<\/p>\n
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.<\/p>\n
<\/p>\n
DATE(YEAR(TODAY()), MONTH(TODAY()) - 6<\/strong>, 1)<\/p>\n
.<\/p>\n
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?<\/p>\n
<\/p>\n
Well let's break this step down just a little further...<\/p>\n
<\/p>\n
We want a result of -6 months.<\/p>\n
If we subtract 1 year (12 months) then add 6 months, that will do the trick.<\/p>\n
<\/p>\n
So we say<\/p>\n
<\/p>\n
DATE(YEAR(TODAY()) - 1, <\/p>\n
<\/p>\n
to subtract the 12 months.<\/p>\n
<\/p>\n
DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)<\/p>\n
<\/p>\n
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<\/p>\n
<\/p>\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))<\/p>\n
<\/p>\n
turn it into a logical statement<\/p>\n
<\/p>\n
@cell<\/a><\/strong> >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))<\/p>\n
<\/p>\n
and drop that into the second portion of our AND statement.<\/p>\n
<\/p>\n
AND(@cell<\/a> <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, logical statement 2<\/strong>)<\/p>\n
<\/p>\n
AND(@cell<\/a> <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell<\/a> >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))<\/strong>)<\/p>\n
.<\/p>\n
So now lets roll on back to our SUMIFS.<\/p>\n
<\/p>\n
We have established our sum range, our first criteria range and our first criteria set.<\/p>\n
<\/p>\n
=SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell<\/a> <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell<\/a> >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))) <\/strong><\/p>\n
.<\/p>\n
All that is left now is adding in the name using the same syntax as above:<\/p>\n
<\/p>\n
criteria range, criteria<\/p>\n
[Name Column]:[Name Column], \"John Smith\"<\/strong><\/p>\n
.<\/p>\n
Since that's our last set of criteria, we can throw it in and close out the SUMIFS...<\/p>\n
<\/p>\n
=SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], AND(@cell<\/a> <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell<\/a> >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), [Name Column]:[Name Column], \"John Smith\"<\/strong>)<\/p>\n
.<\/p>\n
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.<\/p>\n
.<\/p>\n
Does this breakdown work? I'd be more than happy to explain further if you'd like to ask any questions or anything.<\/p>","bodyRaw":"
Happy to help!
<\/p>\n\n<\/p>\n\n
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).<\/p>\n\n
<\/p>\n\n
=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\")<\/p>\n\n
.<\/p>\n\n
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).<\/p>\n\n
We know that the syntax requires our sum range first, so we start with<\/p>\n\n
<\/p>\n\n
=SUMIFS([Points Column]:[Points Column],<\/strong><\/p>\n\n
.<\/p>\n\n
Next we need to establish our first criteria range. In this formula, I started with the dates.<\/p>\n\n
<\/p>\n\n
=SUMIFS([Points Column]:[Points Column], [Date Column]:[Date Column], <\/strong><\/p>\n\n
.<\/p>\n\n
Now we establish our date criteria (this is the tricky part, but I'll break it down step by step).<\/p>\n\n
We know we have two sets of criteria for the same range, so we use an AND statement.<\/p>\n\n
<\/p>\n\n
AND(<\/strong>logical statement 1, logical statement 2)<\/strong><\/p>\n\n
.<\/p>\n\n
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).<\/p>\n\n
<\/p>\n\n
So how can we just go ahead and capture the entire current month? Easy right?<\/p>\n\n
<\/p>\n\n
MONTH(TODAY())<\/p>\n\n
<\/p>\n\n
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.<\/p>\n\n
<\/p>\n\n
But it maintains a consistent format for both sets of criteria so we'll stick with it.<\/p>\n\n
.<\/p>\n\n
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.<\/p>\n\n
<\/p>\n\n
This will account for 28, 29, 30, or 30 days without us having to regularly edit anything.<\/p>\n\n
.<\/p>\n\n
So the first of next month would look something like this...<\/p>\n\n
<\/p>\n\n
DATE(YEAR(TODAY()), MONTH(TODAY()) + 1<\/strong>, 1)<\/p>\n\n
.<\/p>\n\n
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.<\/p>\n\n
<\/p>\n\n
IFERROR(the normal function to run, what to do in the event of an error)<\/p>\n\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)<\/u>, what to do in the event of an error)<\/p>\n\n
.<\/p>\n\n
That first part is where we put our first DATE function. Simply drop it in, and that part is done. <\/p>\n\n
<\/p>\n\n
So if the month is December and we want to go to January 1 of next year, we would say this...<\/p>\n\n
<\/p>\n\n
DATE(YEAR(TODAY()) + 1<\/strong>, 1, 1)<\/p>\n\n
<\/p>\n\n
This gives us Jan 1 of next year, so we will just drop that into the second portion of our IFERROR.<\/p>\n\n
.<\/p>\n\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)<\/u>)<\/p>\n\n
.<\/p>\n\n
Now we have the first of next month established. Subtracting 1 from that will give us the last day of the current month.<\/p>\n\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1<\/strong><\/p>\n\n
.<\/p>\n\n
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.<\/p>\n\n
@cell <= <\/strong>IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1<\/p>\n\n
<\/p>\n\n
AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1<\/u>, logical statement 2)<\/p>\n\n
.<\/p>\n\n
On to the low end of our range...<\/p>\n\n
<\/p>\n\n
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.<\/p>\n\n
<\/p>\n\n
DATE(YEAR(TODAY()), MONTH(TODAY()) - 6<\/strong>, 1)<\/p>\n\n
.<\/p>\n\n
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?<\/p>\n\n
<\/p>\n\n
Well let's break this step down just a little further...<\/p>\n\n
<\/p>\n\n
We want a result of -6 months.<\/p>\n\n
If we subtract 1 year (12 months) then add 6 months, that will do the trick.<\/p>\n\n
<\/p>\n\n
So we say<\/p>\n\n
<\/p>\n\n
DATE(YEAR(TODAY()) - 1, <\/p>\n\n
<\/p>\n\n
to subtract the 12 months.<\/p>\n\n
<\/p>\n\n
DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1)<\/p>\n\n
<\/p>\n\n
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<\/p>\n\n
<\/p>\n\n
IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))<\/p>\n\n
<\/p>\n\n
turn it into a logical statement<\/p>\n\n
<\/p>\n\n
@cell<\/strong> >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))<\/p>\n\n
<\/p>\n\n
and drop that into the second portion of our AND statement.<\/p>\n\n
<\/p>\n\n
AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, logical statement 2<\/strong>)<\/p>\n\n
<\/p>\n\n
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))<\/strong>)<\/p>\n\n
.<\/p>\n\n
So now lets roll on back to our SUMIFS.<\/p>\n\n
<\/p>\n\n
We have established our sum range, our first criteria range and our first criteria set.<\/p>\n\n
<\/p>\n\n
=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))) <\/strong><\/p>\n\n
.<\/p>\n\n
All that is left now is adding in the name using the same syntax as above:<\/p>\n\n
<\/p>\n\n
criteria range, criteria<\/p>\n\n
[Name Column]:[Name Column], \"John Smith\"<\/strong><\/p>\n\n
.<\/p>\n\n
Since that's our last set of criteria, we can throw it in and close out the SUMIFS...<\/p>\n\n
<\/p>\n\n
=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\"<\/strong>)<\/p>\n\n
.<\/p>\n\n
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.<\/p>\n\n
.<\/p>\n\n
Does this breakdown work? I'd be more than happy to explain further if you'd like to ask any questions or anything.<\/p>","format":"html","dateInserted":"2019-08-06T08:16:29+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/194441#Comment_194441","embedType":"quote"}"> 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)
. But I'll give it a try and try to understand how it works.
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T00:32:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T00:32:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
