COUNTIFS function to avoid filling if rows were blank

0

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

Answer
Discuss

Answers

0
Selected Answer

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

Discuss
0

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

Discussion

Your code didn't exactly solve my issue, but it did ring a bell for me, yesterday I solved this issue, but then today I was continuing on as the updated code required that if an employee is listed and has alltogether not been issued these tests this will display No 404's. 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 emplanation of the code will help better understand it's intended purpose, if someone has a similar purpose, use it as needed. Thank you Don for the lead into my modification to existing code.


=IF(B2="","",IF(COUNTIFS(E2,"",K2,""),"No 404's",IF(COUNTIFS(E2,"*Valid*",G2,"*Valid*",J2,"*Y*"),"Valid","Expired")))
worrow Sep 23, '16 at 7:36 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login