Using COUNTIFS for multiple criteria


I wanted to count the number of TASKS that have a STATUS <> Complete where the FINISH date is <= TODAY and I have that formula:

=COUNTIFS(Status:Status, <>"Complete", Finish:Finish, <=TODAY())


Now I want to add Assigned To into this equation. So I want to count the number of TASKS that have a STATUS <> Complete where the FINISH date is <= TODAY … and ASSIGNED TO = “Lindsey Crabtree”


I am having trouble getting this formula to work. I want to do this for each person on the team, there are about 10 people. I am putting this information into a sheet so that I can display it on the dashboard.

Further, once I have this formula figured out, I will need to use another reference sheet to pull the data but I was trying to get it to work first as simply as possible.

Thank you in advance for any help you can give me!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You should be able to add the range and criteria following the same syntax as the previous range/criteria sets.

    =COUNTIFS(Status:Status, <>"Complete", Finish:Finish, <=TODAY(), [Assigned To]:[Assigned To], "Lindsey Crabtree")

  • Thank you so much, that worked! One more question. How do I use this when referencing another sheet? Here is what I have so far.


    =COUNTIFS({SMART 360 Implementation Status}:{SMART 360 Implementation Status}, <>'Complete', {SMART 360 Implementation Finish}:{SMART 360 Implementation Finish}, <=TODAY(), {SMART 360 Implementation Assigned To}:{SMART 360 Implementation Assigned To}, "Jenara Gardner")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It should look more like this:

    =COUNTIFS({SMART 360 Implementation Status}, <>'Complete', {SMART 360 Implementation Finish}, <=TODAY(), {SMART 360 Implementation Assigned To}, "Jenara Gardner")


    If that doesn't work, how exactly are you creating your cross sheet references?

  • This is the formula I have, it is still saying UNPARSEABLE

    They way I created a cross reference sheet was that I made a new sheet in smartsheet where I am putting all my formulas that feed to my dashboard. Does that answer your question? I'm sorry if I'm not giving you the right information.

    =COUNTIFS({SMART 360 Implementation Status}, <> 'Complete', {SMART 360 Implementation Finish}, <=TODAY(), {SMART 360 Implementation Assigned To}, "Jenara Gardner")

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Lindsey (& Hi Paul!)

    Just to jump in here, I notice you have 'Complete' in single quotes. Make sure they're double, like this: "Complete"

    That might be why you're getting Unparseable. 🙂


    =COUNTIFS({SMART 360 Implementation Status}, <> "Complete", {SMART 360 Implementation Finish}, <=TODAY(), {SMART 360 Implementation Assigned To}, "Jenara Gardner")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. With this formula, you have apostrophes instead of quotes around Complete.


    'Complete'

    vs

    "Complete"


    To insert a cross sheet reference in Smartsheet, start typing your formula

    =COUNTIFS(


    Then there should be a little helper box that shows up below your cell that you are typing in. Click on the link to "Reference Another Sheet".


    This will open a new box. Find your sheet you want to reference and select the range you want to reference. To select an entire column, click on the column header.


    Then you can click on the small blue box in the bottom right corner that says "Insert Reference".


    This will take you back to where you are working, and you should now see

    =COUNTIFS({Sheet Name Range 1}


    From there you enter your comma and criteria, throw in another comma, then follow the above steps to insert the next range reference.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!