You may already know Excel can perform calculations with numbers. But did you know it can also perform calculations with other types of information? One of the simplest examples of this is the COUNTA function. COUNTA simply looks at a range of cells and tells you how many of the cells contain data. In other words, it looks for nonblank cells. This can be useful in a variety of situations.
If you've never used Excel functions, check out the Functions lesson in our Excel Formulas tutorial. COUNTA works the same in all versions of Excel, as well as other spreadsheet applications like Google Sheets.
You can download our example if you'd like to work along with this article.
Our example
In our example, we're using Excel to plan an event. We've sent out invitations to everyone, and once we receive their responses, we'll type either Yes or No in column C. As you can see, column C still has some empty cells because we haven't heard back from everyone.
Counting responses
We're going to use COUNTA to see how many people have responded. In cell F2, type an equals sign (=), followed by COUNTA:
=COUNTA
As with any function, the arguments will need to go in parentheses. In this case, we only need one argument: the range of cells we want COUNTA to look at. Our responses are in cells C2:C86, but we can actually include a few extra rows in case we decide to invite more people:
=COUNTA(C2:C100)
When you press Enter, you'll see that we've received 55 responses. Here's the best part: We can continue to update this spreadsheet as we receive responses, and our function will automatically recalculate to give us the correct answer. Try entering Yesor No into some of the empty cells in column C, and you should see the value in cell F2 change.
Counting invitees
We can also use COUNTA to calculate the total number of people that were invited. In cell F3, type the following function and press Enter:
=COUNTA(A2:A100)
See how easy it is? All we had to do was type a different range (A2:A100), and it counts all of the first names, giving us an answer of 85. If you type more names at the bottom of the spreadsheet, Excel will automatically recalculate this value. However, if you type anything below row 100, you'll need to update your ranges so any new people are included.
Bonus question!
We now have the number of responses in cell F2 and the total number of invitees in cell F3. It would be great to calculate the percentage of people who have responded. See if you can write a formula in cell F4 that calculates the percentage.
Remember to use cell references; we want the formula to recalculate whenever we update the spreadsheet.
0 comments:
Post a Comment