Countif in Progress and late

Swamfax

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!

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!