SUMIFS & COUNTIFS in Excel
Learn how to calculate totals and count records using multiple conditions in Excel. This lesson covers syntax, comparison operators, date criteria, text criteria, practical student reports, and common formula mistakes.
1. What Is SUMIFS?
SUMIFS adds numbers only when all specified conditions are satisfied. It is useful for calculating totals for a particular student, course, department, category, or date range.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range: The numeric cells to add.
- criteria_range1: The first range to evaluate.
- criteria1: The first condition.
- Additional criteria: Optional ranges and conditions that must also be satisfied.
2. Practical Dataset
Consider this sample training record table for CIIT Training Institute. Each row represents a student's course and score.
| Student | Course | Batch | Marks | Fee (₹) |
|---|---|---|---|---|
| Samadhan | Advanced Excel | Morning | 85 | 8,000 |
| Samiksha | Advanced Excel | Evening | 92 | 8,000 |
| Pradnya | SQL Server | Morning | 76 | 12,000 |
| Omkar | Advanced Excel | Morning | 68 | 8,000 |
| Neha | SQL Server | Evening | 88 | 12,000 |
| Samadhan | SQL Server | Evening | 90 | 12,000 |
The values above are illustrative training data.
3. SUMIFS with One Condition
Suppose the table is stored in A1:E7. You want to calculate the total fees for students enrolled in Advanced Excel. Course names are in B2:B7 and fees are in E2:E7.
=SUMIFS(E2:E7,B2:B7,"Advanced Excel")
Step-by-step explanation
E2:E7contains the fees to add.B2:B7contains the course names."Advanced Excel"is the condition.- Excel adds fees only for rows matching that course.
Three Advanced Excel records each have a fee of ₹8,000, so the total is ₹24,000.
4. SUMIFS with Multiple Conditions
Now calculate the total fees for students enrolled in Advanced Excel in the Morning batch. Both conditions must be true for a record to be included.
=SUMIFS(E2:E7,B2:B7,"Advanced Excel",C2:C7,"Morning")
Step-by-step explanation
- Excel checks the course name in column B.
- It checks the batch in column C.
- Only records satisfying both conditions are selected.
- The corresponding values in column E are added.
Samadhan and Omkar match both conditions, so ₹8,000 + ₹8,000 = ₹16,000.
Illustration: SUMIFS filters rows using both conditions and then adds the matching fees.
5. Use Cell References in SUMIFS
Instead of hardcoding criteria, you can enter them in cells. Suppose G2 contains Advanced Excel and H2 contains Morning.
=SUMIFS(E2:E7,B2:B7,G2,C2:C7,H2)
Change G2 or H2 to calculate a different course and batch combination without rewriting the formula.
6. SUMIFS with Comparison Operators
Criteria can include comparison operators such as greater than, less than, and greater than or equal to.
Example: Total Marks Above 80
Assume marks are in D2:D7 and you want the sum of marks greater than 80.
=SUMIFS(D2:D7,D2:D7,">80")
Example: Greater Than or Equal to 85
=SUMIFS(D2:D7,D2:D7,">=85")
Example: Use a Cell for the Threshold
If G2 contains 80, the formula below adds marks greater than the threshold in G2.
=SUMIFS(D2:D7,D2:D7,">"&G2)
&.
7. What Is COUNTIFS?
COUNTIFS counts the number of records that satisfy all specified conditions. Unlike SUMIFS, it counts matching records instead of adding numeric values.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example: Count Advanced Excel Students
=COUNTIFS(B2:B7,"Advanced Excel")
Three records in the sample table have Advanced Excel as their course.
8. COUNTIFS with Multiple Conditions
Count students enrolled in Advanced Excel in the Morning batch.
=COUNTIFS(B2:B7,"Advanced Excel",C2:C7,"Morning")
Samadhan and Omkar match both the course and batch conditions.
Example: Count Students with Marks Above 80
=COUNTIFS(D2:D7,">80")
The formula counts the marks greater than 80 in D2:D7. With the sample dataset, the result is 4.
Example: Count with a Cell-Based Threshold
If G2 contains 80, use the following formula:
=COUNTIFS(D2:D7,">"&G2)
9. SUMIFS and COUNTIFS with Dates
You can use date conditions to calculate totals or count records within a date range. Assume transaction dates are in A2:A100 and amounts are in B2:B100.
Example: Sum Amounts from January 2026
=SUMIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))
Example: Count Records from January 2026
=COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))
The start date is included, and the first day of the following month is excluded. This approach also handles date-time values occurring during January.
10. SUMIFS vs COUNTIFS
| Feature | SUMIFS | COUNTIFS |
|---|---|---|
| Purpose | Add matching numeric values. | Count matching records. |
| First argument | The sum range. | The first criteria range. |
| Multiple conditions | Supported. | Supported. |
| Example | Calculate total fees for a course. | Count students enrolled in a course. |
| Criteria logic | All supplied conditions must be satisfied. | All supplied conditions must be satisfied. |
11. Common Mistakes and Solutions
| Problem | Cause | Solution |
|---|---|---|
| Unexpected total | The sum range or criteria ranges are incorrect. | Check each range and the column it refers to. |
| Zero result | No records satisfy every condition. | Check spelling, spaces, criteria, and data types. |
| Incorrect operator condition | The comparison operator is not combined correctly with a cell reference. | Use a formula such as ">"&G2. |
| Incorrect date totals | Dates are stored as text or the date range is wrong. | Use valid Excel dates and check the start and end criteria. |
| Unexpected count | One or more criteria ranges are misaligned. | Use corresponding ranges of compatible dimensions. |
12. Practical Exercises
Exercise 1: SUMIFS
- Calculate the total fees for Advanced Excel.
- Calculate fees for Advanced Excel in the Morning batch.
- Calculate total marks greater than 80.
- Use cell references for the course and batch criteria.
Exercise 2: COUNTIFS
- Count all Advanced Excel records.
- Count Advanced Excel records in the Morning batch.
- Count records with marks greater than or equal to 85.
- Use a cell reference for the marks threshold.
Exercise 3: Date-Based Analysis
- Create a transaction table with dates and amounts.
- Calculate the total for a selected month using SUMIFS.
- Count transactions in that month using COUNTIFS.
- Change the date criteria and verify the results.
Important Points to Remember
- SUMIFS adds values that satisfy multiple conditions.
- COUNTIFS counts records that satisfy multiple conditions.
- All supplied criteria pairs must be satisfied.
- Use comparison operators such as greater than and less than carefully.
- Use cell references to build reusable reports.
- Use valid Excel date values for date-based calculations.
- Ensure that criteria ranges align with the sum range or with one another.
Summary
In this lesson, you learned how SUMIFS calculates totals and COUNTIFS counts records based on one or more conditions. You practiced course fee calculations, marks analysis, comparison operators, cell-based criteria, and date ranges.
These functions help create flexible reports and analyze spreadsheet data efficiently. In the next topic, we will move to Pivot Analytics and explore how PivotTables summarize and analyze larger datasets.