Alternative solution to COUNTIFS required for multiple ranges of different sizes

Hi all,

I'm hoping that someone can assist with a formula that can achieve similar results to the COUNTIFS function, but while referencing ranges of different sizes. I'm hoping to avoid using SUMPRODUCT as there are 2000 rows involved.

I currently receive a table in the following format:

B1:K1 contains a horizontal list of 10 required training courses for new employees
A2:A2000 contains a vertical list of employee ID numbers
B2:K2000 contain a "Y" underneath the name of any course the employee has completed. Eg: if employee listed in A46 has completed the course listed in D1, a "Y" will appear in D46

I'm often required to confirm whether an employee has completed a specific course (from the 10 courses defined in row 1) however scanning the table is a slow and boring process. I'm hoping for a formula that can return a "TRUE" from the above data, if the employee ID and course name data are provided - eg: If I enter the employee's ID number into a cell, and the course name into another, a third cell with return either "TRUE" or "FALSE".


As above, COUNTIFS hasn't worked because not all 3 data ranges are of the same size.


Any help would be greatly appreciated.

With thanks,

Al.

Hi,

a sample file with some data would be welcome.

Regards