Countif in Progress and late

Dear,

I am trying to reference from another sheet (Cloud), tasks that are in progress or not started but late. I tried adapting the below formulas but none is working. Can anyone help me please

=COUNTIF({Cloud Range 1}, "In Progress", "Not Started), [Due Date:Due Date], <Today())

=COUNTIFS({Cloud Range 1}, Status:Status, OR(@cell = "In Progress", @cell = "Not Started"), [Due Date]:[Due Date], < TODAY())

Cheers!

Find more posts tagged with

Sales

Project Management

Comments

Paul Newcome

=COUNTIFS({Cloud Range 1}, OR(@cell = "In Progress", @cell = "Not Started"), [Due Date]:[Due Date], < TODAY())

Give this a try making sure that {Cloud Range 1} covers ONLY the Status column. If the date is on a page separate from the formula, you will also need to use another cross sheet reference to specify {Cloud Range 2} which would be ONLY the Date column.

Mike Wilday

Did you set up that "cloud range" set as you built out the formula? And are the due dates you're trying to reference in the cloud range as well?

Try retyping the formula

=countifs(

...then click on reference data on another sheet in the pop-up to select the status range of the sheet you are referencing.) then add...

OR(@cell = "In Progress", @cell = "Not Started"),

... then click on reference another sheet again, and select the due-date column. Add a comma and put this in after the x-sheet reference...

<TODAY())

Overall you should see something like this... The cross sheet references will be named based on the sheet you referenced or the name you gave them.

=COUNTIFS({Testing Sheet Range 1}, OR(@cell = "In Progress", @cell = "Not Started"), {Testing Sheet Range 2}, <TODAY())

Here is a link to a recording of me doing this process...

https://www.screencast.com/users/UC-Elearning/folders/Snagit/media/038e8c34-93ab-402e-acf2-332007050ed8

Swamfax

Thank you so much for your detailed explanation Mike. it was well explained that I don't even need to watch the recording.

Cheers!

Swamfax

cheers mate!

Swamfax

Dear Mike,

I don't know if you can also assist me in adapting the below formula to generate a status update from reference sheet.

I am trying to create a formula to generate a status update based on end date from a reference sheet (name: Cloud).

=IF({Cloud Range 2},"Complete" <> 1, IF(TODAY() - [End Date] > 0, "Red", IF(TODAY() - [End Date] > -3, "Yellow", "Green"))))"

I have added the header of the sheet I am trying to reference from.

Many thanks in advance.

Swamfax

Thanks. I was able to solve, cheers!

Mike Wilday

Hi Swamfax, Sorry I didn't receive your message! But I am glad you got it worked out!

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.