When we have a bunch of data entries regarding students and we need to clarify for whether a particular student has completed a course or not, here’s what we can do:

To do the same, we will take an example and use the IF and COUNTIFS Function.

Previously: How to round a number down with ROUNDDOWN Formula?

Example:

Course completion status

As you can observe in the image above, we have mentioned 4 roll nos. who have done either course A, course B or course C, or they have taken a combination of the same.

This is how we can know for which student has completed which course.

Course completion status

We will input the IF Function, i.e. =IF(

The following formula requires logical test, value if true, value if false.

Course completion status

In the logical test of IF Function, we will use the COUNTIFS Function.

=COUNTIFS(

The following function requires criteria range 1, criteria 1, criteria range 2, criteria 2 and so on.

Course completion status

Our first criteria range is the roll nos. in the above example, so we will input the same (B:B).

Course completion status

Then we will come to the point of criteria we are finding in the criteria range 1.

As we are finding about Roll No. 5671 so we will select the same, i.e. cell H4, or we can simply write “5671” whichever is comfortable.

Course completion status

Then we will come to criteria range 2, i.e. our course. As we are looking for courses done in relation to the roll nos., so it’s an obvious that our criteria range 2 will be course i.e. C:C.

Course completion status

Then we will choose the course, as we want to check for whether the Roll No. 5671 has completed course A or not, so we will select the same. So, it’s either cell I3 or we can write it in quotation marks which becomes “A”.

Course completion status

Then we will close the COUNTIFS Function, and continue in the IF Function.

The second requirement is the value if true.

Course completion status

If the particular roll no. has done a particular course then what do you expect to show on the screen is what is value if true.

So, if we want it to be written as Done, then we will simply add it in the formula with quotation marks which becomes “Done”.

Course completion status

Then we will come to the value if false, this is the value that you want to show up on the screen if the particular Roll No. not completed a particular course.

So, we will write the following in quotation marks: “Not Done”.

Please remember, you have to put the quotation marks as you are customizing the function on your own. So, excel expects you to write some values in quotation marks, if you ignore the same then your formula will show #VALUE (or in other words – error).

Course completion status

Now all you have to do is close the IF Function, your overall function formula becomes:

=IF(COUNTIFS(B:B,H4,C:C,I3),”Done”,”Not Done”)

Course completion status

And here comes your answer, Done. This means that the Roll No. 5671 has completed Course A.

Course completion status

Now all you have to do is use the IF unction and COUNTIFS Function for the rest of the roll nos. or all you can do is drag down the formula but yes you have to input the formula or bring few changes for different courses.

Course completion status

It’s easy, isn’t it?