COUNTIFS and SUMIFS for Conditional Data Analysis

Count matching records with COUNTIFS and total matching values with SUMIFS, using clear formulas and consistent ranges.

  • COUNTIFS counts rows that satisfy all the criteria you supply.
  • SUMIFS starts with the range to total, followed by criteria-range and criterion pairs.
  • Lock fixed ranges before copying a formula, and keep corresponding ranges the same size.

The video applies both functions to departments, regions, and earnings. The examples below use a clearly defined column layout so you can adapt the formulas to your own worksheet.

Practice formulas, PivotTables, and other analysis tools in the live online Excel Bootcamp.

COUNTIFS or SUMIFS?

Both functions evaluate conditions without requiring you to filter the worksheet manually. COUNTIFS answers questions such as "How many employees work in Accounts?" SUMIFS answers "What is the total earnings amount for employees in Accounts?"

Each additional criteria pair narrows the matching rows. To count employees who are in both the South region and Marketing, supply a condition for each column. Both conditions must be true for the same row.

Set Up the Example Columns

The formulas in this guide assume the following illustrative layout: column A is Department, column B is Region, and column C is Earnings. Row 1 contains headers, and rows 2 through 41 contain records. Replace these ranges with the actual columns and rows in your worksheet.

The video uses an exercise workbook. Its reported totals belong to that workbook; a formula applied to your own data will produce your own totals. You do not need to recreate those totals to use the technique.

Count Records with One Criterion

To count Accounts records, enter:

=COUNTIFS($A$2:$A$41,"Accounts")

The first argument is the range to inspect. The second is the value to match. The dollar signs keep the range fixed when the formula is copied to another cell.

If a department name is in E2, use a cell reference for the criterion:

=COUNTIFS($A$2:$A$41,E2)

Copying this formula down moves E2 to E3 and then E4, while the department range stays fixed. This is useful for a summary with a different department on each row.

Sum Values with One Criterion

To total earnings for Accounts, enter:

=SUMIFS($C$2:$C$41,$A$2:$A$41,"Accounts")

SUMIFS places the sum range first. After that, the criteria arguments follow the same range-and-condition pattern used by COUNTIFS. Check the argument order when moving between the two functions.

Use Multiple Criteria

To count employees who work in Marketing and are in the South region:

=COUNTIFS($A$2:$A$41,"Marketing",$B$2:$B$41,"South")

To sum earnings for those same matching rows:

=SUMIFS($C$2:$C$41,$A$2:$A$41,"Marketing",$B$2:$B$41,"South")

These formulas apply AND logic. They do not count everyone in Marketing plus everyone in the South. A row must satisfy both conditions to be included.

Use Greater Than and Not Equal To

Put a comparison operator and its fixed value inside quotation marks. For example, count Accounts employees whose earnings exceed 100000:

=COUNTIFS($A$2:$A$41,"Accounts",$C$2:$C$41,">100000")

To count records whose region is not East, use:

=COUNTIFS($B$2:$B$41,"<>East")

Check the completeness of your data before interpreting a "not equal to" result. A missing region is not evidence that an employee belongs to a known alternative region.

Check the Result

  1. Confirm that each criteria range covers corresponding rows and has the same dimensions.
  2. Make sure the sum range contains the values you intend to total.
  3. Check spelling and stray spaces in labels such as department or region names.
  4. Inspect a copied formula to confirm that fixed ranges have not shifted.
  5. Manually filter a small sample and compare the matching row count or sum with the formula result.

In the lesson, an unlocked range moves downward when the formula is copied, causing the summary to miss earlier records. Absolute references prevent that specific problem, but they do not fix incorrect labels, incomplete data, or a wrong sum range.

Choose the Right Summary Tool

COUNTIFS and SUMIFS work well when you need formulas in a fixed report layout. When you want to explore totals by different fields or time periods, a PivotTable provides another approach. The PivotTable date grouping and timeline lesson shows how to summarize and filter dates.

For the official argument definitions and examples, see Microsoft's COUNTIFS documentation and SUMIFS documentation.

How to Learn Excel

Build practical, career-focused Excel skills through hands-on training designed for beginners and professionals alike. Learn fundamental tools and workflows that prepare you for real-world projects or industry certification.