Countifs Reference Another Sheet - Help

Options
KALESHIRE
KALESHIRE
edited 12/12/23 in Formulas and Functions

This is the formula I am trying to use with reference to another sheet. Range 5 I want to only count if ticket status is "resolved" and Range 6 if in the month of Dec 2023. Error "Unparsable".

=COUNTIFS({Field EDI Request Sheet Range 5}, [TICKET STATUS], "RESOLVED", [{Field EDI Request Sheet Range 6}, (LAST STATUS DATE}, >=DATE(2023, 11, 30), <=DATE(2024, 1, 1)])

Please help!

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi

    There are a few problems with the syntax and some random characters.

    This is your formula. I have put the problem areas in bold

    =COUNTIFS({Field EDI Request Sheet Range 5}, [TICKET STATUS], "RESOLVED", [{Field EDI Request Sheet Range 6}, (LAST STATUS DATE}, >=DATE(2023, 11, 30), <=DATE(2024, 1, 1)])

    The syntax for COUNTIFS is range1, criteria 1, range 2, criteria 2, range 3, criteria 3.

    Something like this

    =COUNTIFS({Field EDI Request Sheet Range 5}, "RESOLVED", {Field EDI Request Sheet Range 6}, >DATE(2023, 11, 30), {Field EDI Request Sheet Range 6},<DATE(2024, 1, 1))

    Will look in the range you defined as {Field EDI Request Sheet Range 5} and search for "RESOLVED"

    And then in the range you defined as {Field EDI Request Sheet Range 6} and look for a date after 30th November 2023 (note I removed the equals as this would include the 30th, and you just want dates after)

    And finally in the range you defined as {Field EDI Request Sheet Range 6} and look for a date before 1st January 2024 (note I removed the equals again)

    It will count the rows where all three things are true.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!