Handy Excel Tips

This article shows how to get the count using more than one criteria. Additionally, it talks about the “conditional formatting” feature which highlights the important results and spares you the inconvenience of having to look at everything in detail.

Countif function can be used to count the number of entries in some column based on a single condition.Whenever we want to get the count using two or more criteria, we can use the sumif array function.
Suppose you have a list of students in column A and in column B you have the names of subjects and Column C tells you whether the student passed or failed that particular subject. Tests happen every quarter and if a student fails the test three times, he/she won’t be promoted to the next class.

Now we want to count how many times a particular student has failed the given subjects.
We basically have to fill the following table.

We can simply do that using the sumif array formula.We have 3 criteria:
Student’s Name, Subject and Fail.So, the formula in cell G3 would be:
{=SUM(IF(($A$3:$A$86=$F3)*($B$3:$B$86=G$2)*($C$3:$C$86=”Fail”),1,0))}
Because it is an array formula, hit ctrl+shift+enter instead of just pressing enter.
Here, when selected range in column A equals student’s Name and chosen range in column B equals Subject Name and selected range in column C equals “Fail”, Excel puts the “1″ in a temporary array. Then SUM returns the sum of all the 1’s. The complete table would look like:

Now, we can highlight all the numbers that are  greater than or equal to 3, just so that we can see who got promoted at first glance. We can do this using the “Conditional formatting” feature under the “Format” tab by selecting the table region. 

Choose “greater than or equal to” option in the conditional formatting second drag down menu and type in “3″ in the third drop down option. And then using the format button choose any color to highlight the chosen criterion.

As we chose red color, all the numbers greater than or equal to 3 have turned into red as shown below:

Popularity: 30%