Stay Organized Using Countifs
Tutorials Shared by the Internet Community
Total Hits - 25158
Total Votes - 117 votes
Vote Up - 53 votes
Vote Down - 64 votes
Domain - www.versitas.com
Category - MS Excel/Functions
Submitted By - Donna Johnson
Submitted on - 2013-03-20 22:12:27
Have you 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! ...