Previous Weeks Formula
Hello! I need to develop a formula to count the number of opportunities awarded this week and then compare to previous weeks. I have a helper column on my source sheet to list the week number for the awarded date. For Awarded Opportunities This Week the following formula is working:
=COUNTIFS({Opportunity Intake study status}, OR(@cell = "Assessment & Validation", @cell = "Needs Assessment & Strategy", @cell = "Development & Production", @cell = "On-Hold", @cell = "Delivered: Pending Revenue", @cell = "Delivered: Complete"), {Opportunity Intake Awarded Week}, =WEEKNUMBER(TODAY()), {Opportunity Intake Awarded Year1}, =YEAR(TODAY()))
Now I can't get the right result for
Awarded Opportunities Previous 5 Weeks
Awarded Opportunities Current 3 Weeks
Awarded Opportunities Current 2 Weeks
I am trying the following for the "previous 5 weeks":
=COUNTIFS({Opportunity Intake study status}, OR(@cell = "Assessment & Validation", @cell = "Needs Assessment & Strategy", @cell = "Development & Production", @cell = "On-Hold", @cell = "Delivered: Pending Revenue", @cell = "Delivered: Complete"), {Opportunity Intake Awarded Week}, <WEEKNUMBER(TODAY()), {Opportunity Intake Awarded Week}, >=WEEKNUMBER(TODAY() - 5), {Opportunity Intake Awarded Year1}, =YEAR(TODAY()))
Any suggestion? Thank you in advance for your help!
Sofi
Best Answers
-
Hey @SofiRuiz
A few things I might try to trouble shoot
- in the dataset you have, is there any data to return? In other words, can you see a result that the formula should be giving you. If not, add a test case to see if it returns it.
- Let's try to add an @cell. I find sometimes the formula seems to need this.
=COUNTIFS({Opportunity Intake study status}, OR(@cell = "Assessment & Validation", @cell = "Needs Assessment & Strategy", @cell = "Development & Production", @cell = "On-Hold", @cell = "Delivered: Pending Revenue", @cell = "Delivered: Complete"), {Opportunity Intake Awarded Week}, @cell<WEEKNUMBER(TODAY()), {Opportunity Intake Awarded Week}, @cell>=WEEKNUMBER(TODAY())-5, {Opportunity Intake Awarded Year1}, @cell=YEAR(TODAY()))
- Frequently with Date formulas, if the date cell is blank or has an error, it will create problems in other formulas. Check your Year and Weeknumber columns to see if they have errors in them. If yes, you may need to add an IFERROR function to those formulas on your source sheet
- When I have trouble with multiple criteria, I begin to take the terms out of the formula one by one, so I can test which term is causing me problems.
Do any of these work for you? We'll keep working it until we get it right
Kelly
-
Hey @SofiRuiz
In the problem above, the trickier piece of the formula is how to handle December and January. We can manage this using an IF statement which first looks to see if it is January. If the current month is not January, we apply the Month-1 Index/Collect formula
=IF(MONTH(TODAY()) = 1, INDEX(COLLECT({source sheet [Number of Early Opportunities]}, {source sheet [Month & Date]}, ISDATE(@cell), {source sheet [CRM Stage Name]}, CONTAINS("Identifying Opportunity", @cell), {source sheet [Month & Date]}, MONTH(@cell) = 12, {source sheet [Month & Date]}, YEAR(@cell) = YEAR(TODAY()) - 1), 1), INDEX(COLLECT({source sheet [Number of Early Opportunities]}, {source sheet [Month & Date]}, ISDATE(@cell), {source sheet [CRM Stage Name]}, CONTAINS("Identifying Opportunity", @cell), {source sheet [Month & Date]}, MONTH(@cell) = MONTH(TODAY()) - 1, {source sheet [Month & Date]}, YEAR(@cell) = YEAR(TODAY())), 1))
Remember when using cross-sheet references you cannot simply copy paste my formula but you must build each cross reference into your Destination sheet.
If this doesn't work, shout out and we'll work it until we get it right
cheers,
Kelly
Answers
-
Hey @SofiRuiz
I think you have the weeknumber subtraction in the wrong place. As written, it appears it is subtracting 5 days from TODAY(). I moved the -5 outside of the WEEKNUMBER(TODAY()) function.
=COUNTIFS({Opportunity Intake study status}, OR(@cell = "Assessment & Validation", @cell = "Needs Assessment & Strategy", @cell = "Development & Production", @cell = "On-Hold", @cell = "Delivered: Pending Revenue", @cell = "Delivered: Complete"), {Opportunity Intake Awarded Week}, <WEEKNUMBER(TODAY()), {Opportunity Intake Awarded Week}, >=WEEKNUMBER(TODAY())-5, {Opportunity Intake Awarded Year1}, =YEAR(TODAY()))
Does this work for you?
Kelly
-
Hi @Kelly Moore , thank you so much for your response!
Made sense and I tried it but now I am getting #INVALID OPERATION 😕
Any thoughts?
Thanks!
Sofi
-
Hey @SofiRuiz
A few things I might try to trouble shoot
- in the dataset you have, is there any data to return? In other words, can you see a result that the formula should be giving you. If not, add a test case to see if it returns it.
- Let's try to add an @cell. I find sometimes the formula seems to need this.
=COUNTIFS({Opportunity Intake study status}, OR(@cell = "Assessment & Validation", @cell = "Needs Assessment & Strategy", @cell = "Development & Production", @cell = "On-Hold", @cell = "Delivered: Pending Revenue", @cell = "Delivered: Complete"), {Opportunity Intake Awarded Week}, @cell<WEEKNUMBER(TODAY()), {Opportunity Intake Awarded Week}, @cell>=WEEKNUMBER(TODAY())-5, {Opportunity Intake Awarded Year1}, @cell=YEAR(TODAY()))
- Frequently with Date formulas, if the date cell is blank or has an error, it will create problems in other formulas. Check your Year and Weeknumber columns to see if they have errors in them. If yes, you may need to add an IFERROR function to those formulas on your source sheet
- When I have trouble with multiple criteria, I begin to take the terms out of the formula one by one, so I can test which term is causing me problems.
Do any of these work for you? We'll keep working it until we get it right
Kelly
-
Thank you so much Kelly! This solved my issue and I have the number I needed!
You've been extremely helpful!
Can I pick your brain on something else? I have a sheet where I keep monthly records of certain number of opportunities and I want to cross-reference them in another sheet but pulling only the value for the previous month. I was trying to use an INDEX(COLLECT formula but can't make it work.
As an example, if we are in December 2021, in my other sheet I want to collect the No. of Early Opportunities in "1.Identifying Opportunity" CRM Stage for November 2021. Does this make sense?
Thanks again,
Sofi
-
Hey @SofiRuiz
In the problem above, the trickier piece of the formula is how to handle December and January. We can manage this using an IF statement which first looks to see if it is January. If the current month is not January, we apply the Month-1 Index/Collect formula
=IF(MONTH(TODAY()) = 1, INDEX(COLLECT({source sheet [Number of Early Opportunities]}, {source sheet [Month & Date]}, ISDATE(@cell), {source sheet [CRM Stage Name]}, CONTAINS("Identifying Opportunity", @cell), {source sheet [Month & Date]}, MONTH(@cell) = 12, {source sheet [Month & Date]}, YEAR(@cell) = YEAR(TODAY()) - 1), 1), INDEX(COLLECT({source sheet [Number of Early Opportunities]}, {source sheet [Month & Date]}, ISDATE(@cell), {source sheet [CRM Stage Name]}, CONTAINS("Identifying Opportunity", @cell), {source sheet [Month & Date]}, MONTH(@cell) = MONTH(TODAY()) - 1, {source sheet [Month & Date]}, YEAR(@cell) = YEAR(TODAY())), 1))
Remember when using cross-sheet references you cannot simply copy paste my formula but you must build each cross reference into your Destination sheet.
If this doesn't work, shout out and we'll work it until we get it right
cheers,
Kelly
-
@Kelly Moore you are AMAZING! This formula worked perfectly and has been a life saver!
Can't thank you enough! 🤩
-
Always a pleasure. Glad it worked for you
-
Hope you had a pleasant weekend! I have another formula question that I thought you might be able to help me with. I need to pull activity from previous 3 months and current 3 months
For previous 3 months I am using this one and it's working:
=COUNTIFS({Opportunity Intake study status}, OR(@cell = "Assessment & Validation", @cell = "Needs Assessment & Strategy", @cell = "Development & Production", @cell = "On-Hold", @cell = "Delivered: Pending Revenue", @cell = "Delivered: Complete"), {Opportunity Intake Awarded Date}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))
But I am struggling to replicate for current 3 months. Tried this one but I am getting an incorrect result:
=COUNTIFS({Opportunity Intake study status}, OR(@cell = "Assessment & Validation", @cell = "Needs Assessment & Strategy", @cell = "Development & Production", @cell = "On-Hold", @cell = "Delivered: Pending Revenue", @cell = "Delivered: Complete"), {Opportunity Intake Awarded Date}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 10, 1)), @cell = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))
Any thoughts?
Thank you!
Sofi
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!