Countifs

L

Legacy 384599

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top