Previous Weeks Formula

SofiRuiz

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

Kelly Moore

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

Kelly Moore

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

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

SofiRuiz

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

Kelly Moore

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

SofiRuiz

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

Kelly Moore

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

SofiRuiz

@Kelly Moore you are AMAZING! This formula worked perfectly and has been a life saver!

Can't thank you enough! 🤩

Kelly Moore

@SofiRuiz

Always a pleasure. Glad it worked for you

SofiRuiz

@Kelly Moore

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