How To Determine If A Series Of Requirements Are Complete Using IF and SUMPRODUCT In Excel

In this tutorial we are going to use the IF and SUMPRODUCT functions to determine if a series of courses or requirements have been completed for a student or employee. We will also use Conditional Formatting to make the results more visual.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a list of employees or students and I need to determine if each has completed the three courses they are required to finish:

11816-1

I’ve also created a Data Validation drop down list of all the individuals in cell B2:

11816-2

The formula we are going to use in cell B3 is:

=IF(SUMPRODUCT(($B$6:$B$26=B2)*($C$6:$C$26=”Yes”))=3,”Complete”,”Not Complete”)

It is a basic IF statement that says if the result of the SUMPRODUCT function is 3, then enter “Complete”, if not then “Not Complete”. So the key to this formula is the SUMPRODUCT function:

SUMPRODUCT(($B$6:$B$26=B2)*($C$6:$C$26=”Yes”))=3

How SUMPRODUCT works is it takes the SUM of the PRODUCT of two or more sets of variables. In our case our two sets are:

$B$6:$B$26=B2 – this takes the name in cell B2 and compares it to the list of names in column B.

$C$6:$C$26=”Yes” – this takes the Yes or No answers in column C to determine if they are “Yes”.

The result of the first variable shows a TRUE when the name matches cell B2 and is:

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE:FALSE}

The result of the second shows when the entry in column C equals “Yes” and is:

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}

So when you convert those so FALSE = 0 and TURE = 1, you get:

{0;0;0;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0} * {0;0;0;1;1;1;1;1;0;0;0;0;1;1;1;1;1;0;1;0;0}

Which results in:

{0;0;0;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0} = 3

Therefore, there are only three positions in the two sets of results where they are both a 1. And 1 x 1 =1. All the other combinations produce a 0, and the sum of those three 1’s is 3. Any other combination will not equal a 3. So when I choose a person that has completed all three elements, the formula in cell B3 shows “Complete”:

11816-6

An alternative to this method is to enter into column F the following formula:

=IF(SUMPRODUCT(($B$6:$B$26=B6)*($C$6:$C$26=”Yes”))=3,”Complete”,”Not Complete”)

The only difference is that instead of comparing to cell B2, we are comparing each line to the name in the same row in column B. Now when we copy it down, we get the following:

11816-3

Now I’ll add Conditional Formatting to the cells with the following formula:

11816-4

After I set the formatting style I want, here is the result:

11816-5

Anywhere that “Complete” is in all three rows for the same person, the formatting is activated.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

No comments yet.

Leave a Reply