Weekly Computer Tip # 120
23 July 2005
Count the number of cells that meet a particular criteria (Microsoft Excel)
This week I did a bespoke Microsoft Excel training session at a vet school close to Potters Bar. They use Excel daily to manage, retrieve and analyse data about sick animals and wanted to become more productive with less work. We started off with a couple of time-saving tricks and shortcuts and they seemed to love Excel's "AutoCalculate" feature. OK, what is AutoCalculate? (I'm so glad you asked!) It's something I've mentioned before but I see so few people knowing about it, that I think it bears another visit; see http://www.roem.co.uk/tip_26.html. In a nutshell, when you select cells, Excel displays the sum of the range in the status bar. But AutoCalculate can also perform other types of calculations by simply right-clicking the sum in the status bar. My group this week particularly liked "Count", counting the number of selected, filled cells. (Been there? Done that? Counting the number of rows by hand? And being interrupted half way through?)
They then wanted to take it a step further ... count the cells in a column that satisfied a particular condition only. For example, count those animals suffering from asthma or eczema, displayed with a "y" in a specific column on their worksheet. Well, the CountIf function is the perfect solution for this.
Here's how:
- Select the cell in which the outcome is to appear.
- Enter the syntax of the COUNTIF function as follows:
=COUNTIF(range,criteria)
So, if all the animals with asthma are listed with a Y value in range B1 to B50, our function would look like this:
=COUNTIF(B1:B50,"y")
Make sure that you enter the quotation marks, which are required when a result is text.
Finally, if you're also keen to Excel with Excel but the very thought of spending a day in front of a computer in a classroom puts you off ... there are still places left on the next Hands Off seminar, scheduled for 20 October. This seminar will motivate, enthuse and drive you to get a buzz out of using Excel. But don't just take our word for it ...
"I found the seminar extremely interesting and a very different way of learning. I would definitely recommend it to anyone who uses Excel and wants to progress using the various functions to a greater degree. I would not hesitate to go on another hands-off course." Linda Curnow, Lucy Cavendish College, Cambridge
You can read more and find a booking form online: http://www.roem.co.uk/handsoff_excel.html. Oh, and good news! Roem Limited will shortly accept credit and debit card payments to make it even easier to book!
Until next week.
PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.
Home
Training
Support
Clients
Contact Details
News
Disclaimer