Countifs

[Legacy 384599]

Good day,

I am using a special string to group a result of 3 columns which works perfectly. I have applied this formula up to 100 rows for future expansion, however the rows that contain blank cells where it checks still report. How can I use the below function and avoid having empty cells checked and filled?

=IF(COUNTIFS('404''s'!E2,"*Valid*",'404''s'!G2,"*Valid*",'404''s'!J2,"*Y*"),"Valid","Expired")

I really do hope someone can assist at soonest. Or maybe I can use a different string to accomplish the same thing. It checks 3 columns for specific test: "Expired", "Valid" and "Y"

 

[Legacy 384599]

So folks,

I was pulling out my hair over the last couple days trying to remedy this, the formula wasn't always the same, it got to where it was just today. After much digging I have found a solution and hopefully you can keep it in your back pocket for a rainy day that you may require something similar.

=IF(D2="","",IF(COUNTIFS(E2,"*Valid*",G2,"*Valid*",J2"*Y*"),"Valid","Expired"))


You will notice that as frustrating as it was not much had to change to accomplish what I was looking to do. I am only posting this so someone may find this someday as it is off the beaten path of normal.

 

[Legacy 384599]

I have made a new modification to the string, this allows it to do 2 if statements, if those statements do not match it executes the COUNTIFS. Enjoy all. Explanation below the formula.



=IF(B2="","",IF(COUNTIFS(E2,"",L2,""),"No License",IF(COUNTIFS(E2,"*Valid*",I2,"*Valid*",L2,"*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.