Results 1 to 2 of 2

Thread: Alternative solution to COUNTIFS required for multiple ranges of different sizes

  1. #1

    Alternative solution to COUNTIFS required for multiple ranges of different sizes

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,

    Last edited by Alastair; 2017-01-24 at 04:04 AM.

  2. #2
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Excel Version
    Excel 2010

    a sample file with some data would be welcome.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts