• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for counting complete or failed jobs

T

Tom90

Member
Hi All can anyone help me, I have attached a file for anyone to reference, I have tried using the “IF” formula but cannot get my head round it or get it to work correctly, what I am trying to do is record if a job is opened or close by putting a 1 blank or 0.
What you will see is in column “C” you have three dropdown options and depending on which one you pick it will populate columns “E”, “F” and “G” with a completion date,
In Column “I” and “J” when you put the completion date in any date in column “E”, “F” and “G” will go red or green if they are before the date or after the date, I am looking for some way of recording by a 1 against the dates depend if the fail or are completed in time, but I also need to know if they are still open IE being work on
I think I need three columns for fail and three columns for complete with the 1 or 0 in with ever column. Hope you understand what I am trying to achieve if you open the file it may become clearer so I hope someone can help me, Thanks Tom90
 

Attachments

I believe the 3 possible outcomes you want to count are:
Open, Complete, Complete Late

With the logic being
ND complete is blank = Open
ND complete <= ND RBD = Complete
ND complete > ND RBD = Late

If that's true, formula is:
=IF(I3="","Open",IF(I3>MAX(F3:G3),"Complete Late","Complete"))

You can then use some COUNTIF function to count how many you have of each job.
 
Hi Luke, it works a treat thanks and gives me all the info I require, if I could push my luck just once more what would I have to add to the formula if all the cells are blank as just now is see them as “Open” but I would like the answer to show blank.

Thanks Tom
 
Sure thing.
=IF(D3="","",IF(I3="","Open",IF(I3>MAX(F3:G3),"Complete Late","Complete")))
 
Hi Luke sorry for the late reply but I was away all day yesterday at meetings, once again thanks that worked a treat so I am now on the right track with my report thanks to you. Tom
 
You must log in or register to reply here.
Back
Top