Recently, I did an Excel training session for a team of sales representatives that focused part of the day on formulas. It was interesting that although most of the users were intermediate users, they had not created any of the IF statistical functions in Excel. After looking at some of their data, I immediately thought about the COUNTIFS formula.
I was excited to be able to show them these gems to add to their toolbox for data analysis. I started off with a simple example to help them see how they could stay organized using a COUNTIFS formula. I asked if they had ever been faced with the task of keeping a running tally of items that may have a change in status over time? Here is a 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 let 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.
For this scenario, we would have first name in column A, last name in column B, department in column C, date attended safety session in column D, and a “Yes” in column E if the test was passed. For this example, we used 27 rows, with our data in each column in rows 2 through 27. In an area below the data, (row 31) you would have the departments listed in a column with a space in the next column where you would put the count for each.
You would use the following formula in the cell next to the HR department: =COUNTIFS($C$2:$C$27,”HR”,$E$2:$E$27,”Yes”). The formula would do a count only if the employee had attended the class and completed the exam. You could follow up with a simple division to do the percentage in the next cell, too.
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 HR once, then be consistent and type the same wording for this item-don’t type “Hr” or “hr” 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.
Now go COUNT something!
Versitas ( www.versitas.com ) is a leading nationwide software training company delivering onsite and web based training to leading companies in major US markets and around the world.
For additional information, contact firstname.lastname@example.org
Tags: Excel Formulas