So there are 21 students in your class, and you have just finished entering the marks they received in your latest exam (which was for a 100 marks). Curiosity assails you, and you wonder how many of your students got more than 40. Such is life with academicians – they sometimes think of the most random thoughts.

Still – here’s the list for your perusal:

Now, as we pointed out in the earlier post, there is the index finger method. But, as we also pointed out in the post, it is an almost guaranteed migraine inducer. This Excel tutorial points out a solution to your problem, and throws in a bonus – no migraine!

Use the COUNTIF function. The COUNTIF function only counts those values in a range which match a condition within the formula – if the condition is not met, no counting.

Here’s the formula one would use in our example:

Remember – non-numeric parts of your condition must be in inverted commas (“>”), and an ampersand (&) is needed to connect the text with the number (“>”&40).

But apart from that little niggle, you are more or less set to go. Hitting Enter will tell you that 15 students got more than 40 marks – I would consider that a decent performance really – wouldn’t you?

Our professor, of course, will not cease from his musings. Having nothing better to do on a lazy Tuesday afternoon, he wonders how many students got marks between 40 and 60. 

Well, if you happen to be the hapless TA helping out the Professor-with-the-insatiable-curiosity, fret not. And turn instead to the COUNTIFS function. The COUNTIFS function is the proverbial Big Brother to the COUNTIF function, with the ability to deal with more than one condition at a go.

It’ll look something like this:

And what’s more – it’ll return what you wanted it to – the number of students who scored between 40 and 60 – 5 in this case.

Keep these Count formulas in mind when you’re dealing with tricky situations and large amounts of data – it can save you a lot of time, and a lot of head-scratching!