Newbie: Countif (Range) is between two numbers AND has status of 'Complete'

Hi, I've tried this but it's #UNPARSEABLE.

Any help gratefully received Thanks.

=COUNTIFS({CLOSED/ COMPLETED from June 2023 Range 1}, >=[Column4]@row, {CLOSED/ COMPLETED from June 2023 Range 1}, <=[Column5]@row) AND({CLOSED/ COMPLETED from June 2023 Range 3}, HAS("Completed")


I got a number (13) when I didn't have the AND feature, but I do need to also include the status as the Cancelled jobs are better performing than the Completed jobs (which are harder and take longer!)


Thanks

nualamcd

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    COUNTIFS automatically assumes everything is "and", so you don't need to try to work that in (generally speaking). There is also a syntax issue with the HAS function which really shouldn't be needed if you are not using a multi-select dropdown type column for the Status.

    =COUNTIFS({CLOSED/ COMPLETED from June 2023 Range 1}, >=[Column4]@row, {CLOSED/ COMPLETED from June 2023 Range 1}, <=[Column5]@row, {CLOSED/ COMPLETED from June 2023 Range 3}, @cell = "Completed")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!