extra-credit-header-2018

How Excel COUNTIF function saves time when analyzing final grades

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA

When analyzing grades (especially for a large class) the COUNTIF function in Excel can be extremely helpful.

For example, suppose you have entered your final letter grades, and you need to report how many students have earned a certain letter grade. You can easily determine this using the COUNTIF formula.

COUNTIF will count how many items (letters, numbers, or words) within the range you select meet the search criteria you set. Say, for example, you want to know how many students earned a final grade of F in your class, as illustrated in the example below. Begin by typing =COUNTIF( in an empty cell, followed by the range you want to search (C2:C18 in the example below) and the cell that contains that criteria you want to search for (D4 in the example below), and press Enter. (If you prefer, rather than typing the cell names in the formula, left-click on the start of the range and hold and drag to the end, type in a comma, click on the cell with the criteria, type in a close parenthesis, and press Enter.)

countif1


You will then see the total number of the criterion you searched for. In this example, we see the number 3, indicating that three students received a grade of F.

countif2


You can also manually input the range directly into the COUNTIF formula in the cell’s text input bar by placing it in quotation marks. In this example, for instance, you would type the formula COUNTIF(C2:C:18,“F”).

However, in this case, rather than specifying that the criteria was “F” within the COUNTIF formula, we had the formula reference the highlighted cell (D4) instead. When you use COUNTIF this way, you can easily change the letter grade and the count will automatically update, without needing to alter the formula. For example, if you now want to know how many students earned an A, you only need to change the letter grade in the highlighted cell and press Enter. The count automatically updates, as you can see in the screenshot below:

countif3


Although this technique might not seem as helpful with small amounts of data, using it with larger class sizes can be a huge time saver!

Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent, Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.

SPONSORED REPORT

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.