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.
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.
We will input the IF Function, i.e. =IF(
The following formula requires logical test, value if true, value if false.
In the logical test of IF Function, we will use the COUNTIFS Function.
The following function requires criteria range 1, criteria 1, criteria range 2, criteria 2 and so on.
Our first criteria range is the roll nos. in the above example, so we will input the same (B:B).
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.
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.
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”.
Then we will close the COUNTIFS Function, and continue in the IF Function.
The second requirement is the value if true.
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”.
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).
Now all you have to do is close the IF Function, your overall function formula becomes:
And here comes your answer, Done. This means that the Roll No. 5671 has completed Course A.
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.
It’s easy, isn’t it?