200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Originally I had a long question to a problem, but now I have solved my issue with a function I have overlooked. However my problem is that it gives the result even when a cell is blank. What do I add to this string to not output if the cell was blank? Hope someone can help as this formula is applied to row 100, and all the empty rows in the columb with this formula have expired listed in them.
=IF(COUNTIFS(E2,"*Valid*",G2,"*Valid*",J2,"*Y*"),"Valid","Expired")
I required that if an employee is listed and has all together not been issued these tests this it will display No License. This program to sum up is relating to drivers, they need to have a valid license, (E2), completed safe driving (G2) and written the provincial test. If (B2) is blank it stops checking as that indicates no employee, if field is not blank it checks, (E2) & (K2) to see if blank. If those cells are blank it indicates there is an employee but they were never issued a licence, so displays no license, and if (E2) & (K2) have text, it then will move on to examine That if (E2), (G2) display valid and (J2) displays (Y) for yes, this will indicate employee has completed all necessary testing and is valid to drive, otherwise it will display that they are expired. Maybe the explanation of the code will help better understand it's intended purpose, if someone has a similar purpose, use it as needed. I hope everybody understands the long complex code. It is error free.
=IF(B2="","",IF(COUNTIFS(E2,"",L2,""),"No License",IF(COUNTIFS(E2,"*Valid*",I2,"*Valid*",L2,"*Y*"),"Valid","Expired")))
Try something like this:
=IF(OR(ISBLANK(E2),ISBLANK(G2),ISBLANK(J2)),"",IF(COUNTIFS(E2,"*Valid*",G2,"*Valid*",J2,"*Y*"),"Valid","Expired"))or this:
=IF(AND(ISBLANK(E2),ISBLANK(G2),ISBLANK(J2)),"",IF(COUNTIFS(E2,"*Valid*",G2,"*Valid*",J2,"*Y*"),"Valid","Expired"))