Using COUNTIFS for multiple criteria

Lindsey Crabtree

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!

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")

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

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?

Lindsey Crabtree

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.

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

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.