Help writing a complicated IF statement

Hello, hoping someone can help me

I am trying to build a training tracker, the raw data exported from our web ePk website has the following columns:

Employee ID, Employee Name, Course Name

A row is only populated with completed courses, if a course has not been done you don't get a row with that person's name and ID on that possible course in the download. Here is what I need to do:

Each employee will have their own page in the excel sheet and it will have the following columns, all the possible course names will be under the course name column.

Course Name, Complete?

I need to populate a "YES" in the "Complete?" column by looking at the downloaded sheet and if on the same row employee # "1234" = employee #"1234" in the download sheet AND Course Name = Course Name in the download sheet.

Basically an IF statement that looks for two criteria that must be on the same row to be true. Is this possible?

Hi,

Is this the sort of thing that you are looking for???

Hope this helps

Oh WOW! That is exactly what I needed.

So I know, whats going on here? Never used SUMPRODUCT before.....

Originally Posted by ajryan88

Hi,

Is this the sort of thing that you are looking for???

Hope this helps

I'm glad that worked for you!

OK, now for the complicated explanation:

SUMPRODUCT will multiply the components of multiple arrays together and then add the results to give you a single answer. So in this case, I have fed 2 arrays into the formula.

Formula:

$B$1=Sheet1!$A$2:$A$6

will return an array of true/false values depending on whether or not the employee ID from the downloaded data matches the current employer's ID.

Formula:

'Person 1'!A3=Sheet1!$C$2:$C$6

will return an array of true/false values depending on whether or not the course name from the downloaded data matches the current course. By placing the "--" in front of each of these arrays, it converts all TRUE values to 1 and all FALSE values to 0, as SUMPRODUCT needs numbers fed into the arrays.
Now for an example of how these arrays will be treated by SUMPRODUCT, consider these 2 simple arrays: {1,2,3} and {10,11,12}. SUMPRODUCT will give an answer of

Formula:

(1 x 10) + (2 x 11) + (3 x 12) = 10 + 22 + 36 = 68

Hopefully you can see now that the results of the SUMPRODUCT on the workbook will either be 1 if an emplyee ID AND a course name match, or 0 if there are no matches. Hence the conditions of the IF formula that I used to return the desired result.

I'm sorry if this is still a bit unclear to you, I never was a good teacher, I just knew how to do stuff!

Please don't forget to mark this thread as solved and click the * next to my post to say thanks

Have a great day!