Excel COUNTIFs and SUMIFs

Logic Functions are covered extensively in our NYC Excel courses. For those outside New York, find and compare the best Excel training near you and virtual/remote online Excel courses.

COUNTIFS and SUMIFS

SUMIFS and COUNTIFS are among Excel's most powerful analytical functions, enabling you to count and sum data based on multiple conditions simultaneously. Think of these functions as sophisticated filters that can perform calculations while they work. When any Excel function includes "IF" as a suffix, it operates as a conditional filter—and mastering this concept is crucial for data analysis in 2026's increasingly complex business environment.

To illustrate how these functions replicate filtering operations, let's examine what happens when you manually filter a table to achieve the same results.

COUNTIFS

First, I'll demonstrate the filtering approach. Clicking anywhere within the table, I navigate to the Data tab and apply a filter. The objective here is to determine the total number of times "accounts" appears in the department column.

By clicking the dropdown and filtering exclusively for "accounts," then selecting "OK," the result is 10. The status indicator in the bottom left confirms this: "10 of 40 records found." This manual approach works for simple analyses, but becomes unwieldy when dealing with multiple criteria or dynamic reporting requirements.

SUMIFS

SUMIFS extends this concept by aggregating values from a separate column based on your filtered criteria. Instead of merely counting occurrences, you're calculating totals. When I select the corresponding values in the earnings column, the status bar displays 952,500—this is SUMIFS in action, performed manually.

The key advantage of using SUMIFS and COUNTIFS functions over manual filtering becomes apparent when you need to create dynamic reports, dashboards, or when working with datasets that update regularly.

Exercise 1

Now I'll clear the filter and achieve identical results using functions instead of manual filtering. Moving to the employees section, the goal is to calculate the total number of employees by counting occurrences of "accounts" in the department column.

I'll type =COUNTIFS. Here's a critical best practice: always use the plural versions (COUNTIFS, SUMIFS) rather than their singular counterparts. The plural versions offer significantly more flexibility, allowing multiple column criteria ranges, while COUNTIF restricts you to a single column—a limitation that often becomes problematic as your analysis needs evolve.

Selecting the department column with Ctrl+Shift+Down, I highlight the entire range and enter a comma. Notice the function's intuitive structure: I'm currently at the criteria range parameter. After entering the comma, I specify the criteria—equivalent to selecting "accounts" in the filter checkbox.

After pressing Enter, the result is 10. When I autofill this formula down, it should calculate employee counts for all departments. However, there's a critical issue to address.

Examining the "sales" cell and pressing F2 reveals that the column reference excludes the first five rows. This occurs because I shifted down five rows, and Excel uses relative references by default. This is a common error that can invalidate your entire analysis.

For COUNTIFS and SUMIFS functions, always lock your ranges using absolute references. Returning to the original function, I press F4 to lock the range, then press Enter. Double-clicking to autofill demonstrates the dramatic difference in the final values when ranges are properly locked—this small detail can make or break your data analysis.

Exercise 2

Moving to SUMIFS, the syntax follows a logical pattern that builds on COUNTIFS knowledge. I'll type =SUMIFS, and the first parameter requests the range to sum. Selecting the earnings column with Ctrl+Shift+Down and pressing F4 to lock the range, I enter a comma.

Here's the elegant connection: everything following the first column in SUMIFS mirrors the COUNTIFS function structure. This consistency makes SUMIFS intuitive once you've mastered COUNTIFS.

Navigating to the department column, applying Ctrl+Shift+Down, and pressing F4, I specify "accounts" as the criteria, just as in the COUNTIFS function. Closing parentheses and pressing Enter yields 952,000—confirming our earlier manual calculation.

Now we'll leverage the multiple criteria capability that makes these functions truly powerful. The question: "How many employees from the south region work in the marketing department?"

Using COUNTIFS with the region as the first criteria range, I press F4 and specify "south" as the target value. To find employees who are both in the south region AND the marketing department, I add the department column as a second criteria range, press F4, add a comma, and specify "marketing."

This dual criteria approach is equivalent to applying checkboxes in both column dropdowns simultaneously. The result: four employees meet both conditions.

To calculate the combined salary of these four employees, I'll use SUMIFS. The sum range targets "earnings" (F4 for absolute reference), with region as the first criteria range (F4, "south"), and department as the second criteria range (F4, "marketing"). The result: $495,000.

Exercise 3

Advanced criteria specification allows for comparative operations—greater than, less than, or not equal to specific values. This functionality transforms these functions from simple matching tools into sophisticated analytical instruments.

For this exercise, we'll count employees earning more than $100,000. Using =COUNTIFS, I select the department column (F4), reference the first row without locking it (allowing the reference to shift as I copy the formula), then add earnings as the second criteria range.

The key syntax for comparative criteria: ">100000" in double quotes specifies "greater than $100,000." The result shows three people in the accounts department earn above this threshold.

Exercise 4

The corresponding SUMIFS calculation determines the total compensation for high earners in each department. Starting with =SUMIFS, I select the sum range (earnings column, F4), then mirror the COUNTIFS structure: department column (F4), left-cell reference for department names, earnings column again (F4), and ">100000" as the criteria.

The result of $376,000 represents the combined salaries of high earners, and autofilling extends this analysis across all departments.

Bonus Exercise

For exclusionary criteria, Excel uses "<>" to indicate "not equal to." To count employees outside the east region: =COUNTIFS, region column (Ctrl+Shift+Up and Down arrow, F4), criteria "<>east". The result of 28 represents all employees not in the eastern region.

This technique is particularly valuable for exception reporting and identifying outliers in your datasets.

Recap

COUNTIFS and SUMIFS represent essential tools for modern data analysis, enabling rapid calculation of counts and sums based on single or multiple column criteria. In today's data-driven business environment, these functions provide the foundation for dynamic reporting, dashboard creation, and sophisticated analysis that would be time-prohibitive using manual filtering methods.

The key to mastering these functions lies in understanding their logical structure, consistently using absolute references for data ranges, and leveraging their multiple criteria capabilities to answer complex business questions efficiently.