Countifs after a date

I am trying to count the number of closed opportunities for the 2021 calendar year that meet the criteria of: Won AND is of market sector offices new. I have used the formula below but it's not working. Where have I gone wrong?

=COUNTIFS({Sheet - Sales Pipeline Sales Stage}, "Closed Won", {Sheet - Closed Opps Market Sector}, "OFF_OFF Offices New", AND{Perth Closed Opportunity Submission Due Date}, >= (1 / 1 / 21))

Tags:

Best Answer

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    Good morning @Sarah Dawson ,

    Your formula looks well put together, the only hiccups I see are the AND in the last section and the way you show the date. The COUNTIF formula will automatically look for items that meet all of the criteria, no need for the AND. Additionally, if you do use AND in the future, just double check that you are using parenthesis around the function.

    For using specific dates in a formula try:

    =DATE(2021, 1, 1)
    

    Here is your formula with corrections, let me know if this helps!

    =COUNTIFS({Sheet - Sales Pipeline Sales Stage}, "Closed Won", {Sheet - Closed Opps Market Sector}, "OFF_OFF Offices New", {Perth Closed Opportunity Submission Due Date}, >=DATE(2021, 1, 1) )
    

    Best,

    Lidiya Shutaya

    lidiya@ddbconsultants.ca

Answers

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    Good morning @Sarah Dawson ,

    Your formula looks well put together, the only hiccups I see are the AND in the last section and the way you show the date. The COUNTIF formula will automatically look for items that meet all of the criteria, no need for the AND. Additionally, if you do use AND in the future, just double check that you are using parenthesis around the function.

    For using specific dates in a formula try:

    =DATE(2021, 1, 1)
    

    Here is your formula with corrections, let me know if this helps!

    =COUNTIFS({Sheet - Sales Pipeline Sales Stage}, "Closed Won", {Sheet - Closed Opps Market Sector}, "OFF_OFF Offices New", {Perth Closed Opportunity Submission Due Date}, >=DATE(2021, 1, 1) )
    

    Best,

    Lidiya Shutaya

    lidiya@ddbconsultants.ca

  • That worked great. Thanks for your help.

  • Lidiya S.
    Lidiya S. ✭✭✭

    @Sarah Dawson Glad it worked! Let me know if you have any other questions :)

    Lidiya Shutaya

    lidiya@ddbconsultants.ca

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!