Many of us work with spreadsheets all day long, but we sometimes overlook the power or the formula. As a result, we wind up wasting time doing things manually when our computers are far better equipped to perform these tasks than we are.
Here is a simple tip that allows you to count cells in an Excel spreadsheet. Let’s suppose you have a list of students. Some of them have passed and others have failed, and you have a spreadsheet that looks something like this:
| STUDENT | RESULT |
| Alberto | PASS |
| Angela | PASS |
| Anne-Marie | PASS |
| Ben | PASS |
| Carlos | PASS |
| Dale | PASS |
| Edmund | FAIL |
| Fermin | PASS |
| Hussein | PASS |
| Ibrahim | FAIL |
| Joe | FAIL |
| John | PASS |
| Kathie | FAIL |
| Lakeisha | FAIL |
| Linnea | PASS |
| Mach | PASS |
| Mary | FAIL |
| Peter | PASS |
| Rosemary | FAIL |
| Wendy | PASS |
| TOTAL | 20 |
| PASS | 13 |
| FAIL | 7 |
So, how did we total the numbers in the bottom three cells?
- The first is the total number of students in the class. We can create a formula that looks at all the cells (A2 to A21) in the first column except the top one (A1) and counts any cell that is not blank. We do it by putting the following formula in the cell, B21, where the total (20) is reported:
=COUNTA(A2:A21) - The second is the number of students who have passed. So we have to count all the cells except the top one (B1) if they contain the word “PASS.” Otherwise, we don’t count them. This conditional counting is accomplished with this formula:
=COUNTIF(B2:B21, “PASS”) - Finally, we want to count the number of students who have failed. The formula is almost the same as the way we created the formula for the people who passed:
=COUNTIF(B2:B21, “FAIL”)
Using formulas can save you a lot of time, and the machine can do it much more accurately than you can. Follow the principle that you should never buy a dog and bark yourself!
You can become very sophisticated with the way you use formulas in Excel, and the university offers classes as part of your benefits package. You can take instructor-led courses or choose courses in Microsoft Office by going to our website: http://learning.jhu.edu.
Just remember that there are many versions of Excel. Make sure that you take the course that matches the computer you use and the version that’s installed on your machine.
