Tag Archives: Excel 2007/2010 features

Stay Organized by Using the Excel COUNTIFS Function

Have you ever been faced with the task of keeping a running tally of items that may have a change in status over time? If so, the COUNTIFS function can help you stay current with the status of your data/population.

Scenario: Let’s say your company has implemented a policy that requires all employees to attend a training session on safety and then to pass the safety exam that follows. Let’s say that employees are expected to attend the required training session, which will be offered over a five-month period during the year. Additionally, the exam must be taken and passed by the end of the calendar year.
If your company has different departments and wants to see how many people have attended one of the training sessions and also wants to find out how many people from the various departments have passed the exam, someone will need to go through the data periodically and determine who has attended by department and who has passed by department. This is not hard to do if you have a few employees, but let’s say there are over 200 people who work in the company, and remember they work in different departments! So much for tick marks!
The COUNTIFS function can help you look at multiple conditions at once. If you want to determine (1) who has attended by department and (2) who has actually passed the exam by department, the COUNTIFS (not to be confused with the COUNTIF function, which only looks at one condition) can be a life saver if you have large amounts of data to analyze.

However, there are two important guidelines that must be observed–(1) the ranges you are examining must be consistent in terms of the way data is entered, e.g., use the same case and wording—if you use IT once, then be consistent and type the same wording for this item—don’t type It. or it the next time; (2) the Criteria Ranges should be made absolute ranges, e.g., $C$3:$C$350 or the ranges should be given specific names so they remain constant when copied.

You will find as you update your data—in this example the date a session was attended and if a person passed the exam, you can get your updated information by totals for each department.
The “Percentage Completed by Department” was calculated by using the COUNTIF function of
=(E31/COUNTIF($C$2:$C$27,”IT”))
For more information on BOSS software offerings, the BOSS degree and certificates, and how the BOSS program can help you with your career, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.

Let Excel Be Your Helping Hand With Proofreading Tasks!

Have you ever needed a helping hand?–someone else to help you double-check information–but found that no one was around to help?

Well, Excel has a built-in “Speak Cells” audio feature that can help you as you proofread numbers and other information by sounding out the information for you. You can choose whether you want the information repeated by column or by row.

If you want to add the “Speak Cells” icons to your Quick Access Toolbar (QAT), simply click the down arrow to the right of the QAT and then click on the selection More Commands… When that dialog box opens, find the Choose Commands area and click the drop down arrow next to Popular Commands and change to All Commands.

If you are like most people, you will be surprised to see the expanded array of Excel commands that are available to you. It’s amazing to see what is under Excel’s hood!

Next, scroll down (the commands are listed in alpha order) until you see the commands for Speak Cells. Suggestion: The image below shows a list of Speak Cells icons to consider adding to your QAT. Adding these icons will increase your flexibility in using the Speak Cells feature. Simply select the desired icon(s) and click OK after each selection.

Click on Excel Speak Cells Feature to view and listen to a demonstration of how this feature works.

For more information on BOSS software offerings, the BOSS degree and certificates, and how the BOSS program can help you with your career, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.