Microsoft Excel 2007 to 2013 - CountIF in Excel

<div dir="ltr" style="text-align: left;" trbidi="on"> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Another useful function that uses Conditional Logic is CountIF. This one is fairly straightforward. As its name suggests, it counts things! But it counts things IF a condition is met. For example, keep a count of how many students have an A Grade.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> To get you started with this function, we'll use our Student Grade spreadsheet and count how many students have a score of 70 or above. First, add the following label to your spreadsheet:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="A new label has been added to the K column" border="1" height="205" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF.gif" width="428" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> As you can see, we've put our new label at the start of the K column.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We can now use the CountIF function to see how many of the students scored 70 or above for a given subject.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The CountIF function looks like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>COUNTIF(</b><span style="color: red;">range</span>, <span style="color: blue;">criteria</span><b>)</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The function takes two arguments (the words in the round brackets). The first argument is range, and this means the range of cells you want Excel to count. Criteria means, "What do you want Excel to look for when it's counting?".</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So click inside cell K2, and then click inside the formula bar at the top. Enter the following formula:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>=CountIf(</b><span style="color: red;">B2:I2</span><b>, "</b><span style="color: blue;">>= 70</span><b>")</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The cells B2 to I2 contain the Math scores for all 8 students. It's these scores we want to count.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Press the enter key on your keyboard. Excel should give you an answer of 4:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="A CountIF Function in Excel 2007" border="1" height="110" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF2.gif" width="469" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> (If you're wondering where the columns B to I have gone in the image above, we've hidden then for convenience sake!)</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> To do the rest of the scores, you can use AutoFill. You should then have a K column that looks like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="186" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF3.gif" width="469" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> By using CountIF, we can see at a glance which subjects students are doing well in, and which subjects they are struggling in.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>Exercise</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Add a new label to the L column. In the cells L2 to L9, work out how many students got below 50 for a given subject. You should get the same results as in the image below:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="187" src="http://www.homeandlearn.co.uk/excel2007/images/2CountIF4.gif" width="582" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In the next part, we'll look at a function similar to CountIF - SumIF.</div> </div>

Microsoft Excel 2007 to 2013 - CountIF in Excel

Another useful function that uses Conditional Logic is CountIF. This one is fairly straightforward. As its name suggests, it counts things...

Read more »