COUNTIFS with date range as criteria.


I am trying to use the COUNTIFS formula with a date range. Here is what I have:

  • 2 sheets
    • Attainment sheet (This is where the formula is)
    • Schedule sheet
  • On the Schedule sheet, I am looking at 2 columns
    • Schedule Range 2 = Team column
    • Schedule Range 3 = Service Date column
  • I am trying to see if the Team column contains a string of text.
  • If it does, then count the number of items within a DATE range in the Service Date column.

=COUNTIFS({Schedule Range 2}, CONTAINS("ALBQCC01", @cell), {Schedule Range 3} AND(@cell >= DATE(2021, 02, 01), @cell <= DATE(2021, 02, 07)))

I am getting an #UNPARSEABLE error with the above code.

Thank you,

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Justin Mauzy 

    Hope you are fine, please try the following formula:

    =IFERROR(COUNTIFS({Schedule Range 2}, CONTAINS("ALBQCC01", @cell), {Schedule Range 3},
    AND(@cell >= DATE(2021, 2, 1), @cell <= DATE(2021, 2, 7))), "")

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!