Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

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.

Learning objectives: Understand SUMIFS and COUNTIFS, calculate totals based on multiple conditions, count matching records, work with dates and comparison operators, and build practical training reports.

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.

Syntax
=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.
Important: SUMIFS uses AND logic between its criteria pairs. A row must satisfy every specified condition to contribute to the total.

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.

Excel Formula
=SUMIFS(E2:E7,B2:B7,"Advanced Excel")

Step-by-step explanation

  1. E2:E7 contains the fees to add.
  2. B2:B7 contains the course names.
  3. "Advanced Excel" is the condition.
  4. Excel adds fees only for rows matching that course.
Result: ₹24,000

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.

Excel Formula
=SUMIFS(E2:E7,B2:B7,"Advanced Excel",C2:C7,"Morning")

Step-by-step explanation

  1. Excel checks the course name in column B.
  2. It checks the batch in column C.
  3. Only records satisfying both conditions are selected.
  4. The corresponding values in column E are added.
Result: ₹16,000

Samadhan and Omkar match both conditions, so ₹8,000 + ₹8,000 = ₹16,000.

SUMIFS: Apply Multiple Conditions Condition 1 Course = Advanced Excel Condition 2 Batch = Morning Add matching fees ₹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.

Excel Formula
=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.

Practical tip: Cell references make reports interactive and easier to maintain.

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.

Excel Formula
=SUMIFS(D2:D7,D2:D7,">80")

Example: Greater Than or Equal to 85

Excel Formula
=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.

Excel Formula
=SUMIFS(D2:D7,D2:D7,">"&G2)
Remember: When combining an operator with a cell reference, place the operator in quotation marks and join it to the cell reference using &.

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.

Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example: Count Advanced Excel Students

Excel Formula
=COUNTIFS(B2:B7,"Advanced Excel")
Result: 3

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.

Excel Formula
=COUNTIFS(B2:B7,"Advanced Excel",C2:C7,"Morning")
Result: 2

Samadhan and Omkar match both the course and batch conditions.

Example: Count Students with Marks Above 80

Excel Formula
=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:

Excel 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

Excel Formula
=SUMIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))

Example: Count Records from January 2026

Excel Formula
=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.

Important: Store dates as actual Excel date values rather than text. The date conditions may not work correctly if the source cells contain text that only looks like a date.

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

  1. Calculate the total fees for Advanced Excel.
  2. Calculate fees for Advanced Excel in the Morning batch.
  3. Calculate total marks greater than 80.
  4. Use cell references for the course and batch criteria.

Exercise 2: COUNTIFS

  1. Count all Advanced Excel records.
  2. Count Advanced Excel records in the Morning batch.
  3. Count records with marks greater than or equal to 85.
  4. Use a cell reference for the marks threshold.

Exercise 3: Date-Based Analysis

  1. Create a transaction table with dates and amounts.
  2. Calculate the total for a selected month using SUMIFS.
  3. Count transactions in that month using COUNTIFS.
  4. 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.