COUNTIF function in Excel

I have talked about creating IF and AND statements together in Excel before, but I suddenly realised that I have not talked about creating "COUNT" "IF", or COUNTIF, statements.

COUNTIF is a jolly useful function to have at our fingertips. I use it to count cells that contain a specific thing: for example, if I wanted to know how often a particular grade turned up in my final grade column, I could set in my 'dashboard' area - which I always create at the top of my spreadsheets - the formula =COUNTIF(M:M,"A"). That would count every incidence of the Grade "A" anywhere in column M, no matter how far down the sheet it appeared. However, it would also turn up every heading that I put in Column M or every formula that contained the letter 'A' or 'a' (Excel doesn't differentiate between the cases, unfortunately).

If I wanted to count from row 3 down to row 500, I would modify the formula to read =COUNTIF(M3:M500,"A"). If I had this formula in cell B1, and there were 5 grades which showed the letter A in column M, B1 would show 5. I could do the same in B2 for B, etc, then I would be able to do a graph from the COUNTIF results to show the grade spread.

To create a COUNTIF function, there are two conditions we have to set: where we want the function to look (the range), and what we want it to look for (the condition). In my example, "A" is in quote marks to show it is text that I am wanting the Excel formula to seek. If I was looking for a number, I would simply leave out the quote marks.

There are many, many things you can do with COUNTIF. You can also do a countif in an autosum function, which I will have a look at in another post.

Have a play!


Sam