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.