MS Excel - Statistical Functions in Excel: COUNTIF, COUNTIFS, SUMIF and SUMIFS

Excel provides several functions for analyzing data according to specific conditions. Among the most useful are COUNTIF, COUNTIFS, SUMIF, and SUMIFS. These functions allow users to count records or calculate totals based on one or more criteria.

They are especially useful when working with student marks, employee records, sales data, attendance sheets, inventory lists, and financial reports.

1. COUNTIF Function

The COUNTIF function counts the number of cells in a range that satisfy a single condition.

Syntax

=COUNTIF(range, criteria)

Here:

range is the group of cells that Excel examines.
criteria is the condition that determines which cells should be counted.

Example

Suppose the following student results are stored in column B:

Student	Result
Ravi	Pass
Anu	Fail
Kiran	Pass
Meena	Pass
Arun	Fail

To count how many students passed:

=COUNTIF(B2:B6,"Pass")

The result will be:

Excel searches B2:B6 and counts every cell containing Pass.

COUNTIF with Numbers

COUNTIF can also count numbers according to a condition.

Suppose marks are stored in A2:A10. To count students who scored at least 50:

=COUNTIF(A2:A10,">=50")

To count students who scored below 40:

=COUNTIF(A2:A10,"<40")

To count students who scored exactly 75:

=COUNTIF(A2:A10,75)

COUNTIF with Text

COUNTIF can also count cells containing particular text.

=COUNTIF(C2:C20,"Bangalore")

This counts the number of cells containing Bangalore.

It can be useful for finding how many employees belong to a particular city, how many products belong to a particular category, or how many customers are from a particular location.

COUNTIF with Cell References

Instead of writing the condition directly into the formula, the condition can be stored in another cell.

Suppose E2 contains:

Pass

The formula can be written as:

=COUNTIF(B2:B20,E2)

This makes the worksheet more flexible because changing E2 automatically changes the counting condition.

2. COUNTIFS Function

The COUNTIFS function is used when you need to count cells or records that satisfy multiple conditions.

Syntax

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

Unlike COUNTIF, which works with one criterion, COUNTIFS can evaluate several criteria simultaneously.

Example

Suppose an employee table contains:

Employee	Department	Status	Salary
Ravi	Sales	Active	40000
Anu	HR	Active	45000
Kiran	Sales	Inactive	35000
Meena	Sales	Active	50000
Arun	HR	Active	38000

To count employees who belong to the Sales department and are Active:

=COUNTIFS(B2:B6,"Sales",C2:C6,"Active")

The result is:

Both conditions must be satisfied for a record to be counted.

COUNTIFS with Numeric Conditions

Suppose you want to count employees in Sales whose salary is greater than 40000:

=COUNTIFS(B2:B6,"Sales",D2:D6,">40000")

Excel checks both conditions:

Department must be Sales.
Salary must be greater than 40000.

Only records satisfying both conditions are counted.

COUNTIFS with Dates

COUNTIFS is also useful for counting records within a date range.

For example:

=COUNTIFS(A2:A100,">=01/01/2026",A2:A100,"<=31/01/2026")

This counts dates falling between January 1 and January 31, 2026.

For professional worksheets, it is often better to place the beginning and ending dates in separate cells and refer to those cells in the formula.

3. SUMIF Function

The SUMIF function adds values that meet a specified condition.

While COUNTIF counts matching records, SUMIF calculates the total of matching values.

Syntax

=SUMIF(range, criteria, sum_range)

The arguments are:

range: The cells that Excel examines to determine whether the condition is satisfied.
criteria: The condition to be applied.
sum_range: The cells containing the values to be added.

Example

Consider the following sales data:

Product	Region	Sales
Laptop	South	50000
Mouse	North	5000
Laptop	South	60000
Keyboard	East	7000
Laptop	North	45000

To calculate total sales for the Laptop product:

=SUMIF(A2:A6,"Laptop",C2:C6)

Excel finds all rows where the product is Laptop and adds their corresponding sales values.

The result is:

SUMIF with Numeric Criteria

Suppose a worksheet contains employee salaries in column B. To calculate the total of salaries greater than 50000:

=SUMIF(B2:B20,">50000",B2:B20)

The same range is used both to test the condition and to calculate the sum.

SUMIF with Text Criteria

Suppose column A contains departments and column C contains salaries:

=SUMIF(A2:A20,"HR",C2:C20)

This calculates the total salary of employees belonging to the HR department.

SUMIF with a Cell Reference

If E2 contains the department name HR, you can write:

=SUMIF(A2:A20,E2,C2:C20)

Changing E2 to another department automatically changes the calculation.

4. SUMIFS Function

The SUMIFS function is used when values need to be added based on multiple conditions.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

One important difference is the order of arguments.

For SUMIF:

=SUMIF(range,criteria,sum_range)

For SUMIFS:

=SUMIFS(sum_range,criteria_range1,criteria1,...)

Example

Consider this sales table:

Product	Region	Salesperson	Sales
Laptop	South	Ravi	50000
Laptop	North	Anu	45000
Mouse	South	Ravi	5000
Laptop	South	Kiran	60000
Keyboard	South	Meena	7000

Suppose you want to calculate the total sales of Laptop products in the South region.

The formula is:

=SUMIFS(D2:D6,A2:A6,"Laptop",B2:B6,"South")

Excel checks:

Product must be Laptop.
Region must be South.

It then adds the sales values for records satisfying both conditions.

The result is:

SUMIFS with Three Conditions

SUMIFS can handle more than two conditions.

For example, suppose you want the total sales where:

Product = Laptop
Region = South
Salesperson = Kiran

The formula is:

=SUMIFS(D2:D6,A2:A6,"Laptop",B2:B6,"South",C2:C6,"Kiran")

Only the records satisfying all three conditions are included in the total.

Difference Between COUNTIF and COUNTIFS

The main difference is the number of conditions.

Function	Purpose
COUNTIF	Counts records based on one condition
COUNTIFS	Counts records based on multiple conditions

Example:

=COUNTIF(B2:B20,"Sales")

counts all Sales department employees.

=COUNTIFS(B2:B20,"Sales",C2:C20,"Active")

counts only employees who are both in Sales and Active.

Difference Between SUMIF and SUMIFS

Similarly:

Function	Purpose
SUMIF	Adds values based on one condition
SUMIFS	Adds values based on multiple conditions

Example:

=SUMIF(A2:A20,"Laptop",C2:C20)

calculates the sales of Laptop products.

=SUMIFS(C2:C20,A2:A20,"Laptop",B2:B20,"South")

calculates Laptop sales specifically from the South region.

Using Comparison Operators

These functions can use comparison operators to create numerical conditions.

Operator	Meaning	Example
`=`	Equal to	`"=50"`
`>`	Greater than	`">50"`
`<`	Less than	`"<50"`
`>=`	Greater than or equal to	`">=50"`
`<=`	Less than or equal to	`"<=50"`
`<>`	Not equal to	`"<>50"`

For example:

=COUNTIF(A2:A50,">=60")

counts values of 60 or more.

Similarly:

=SUMIF(A2:A50,">=60",B2:B50)

adds the corresponding values from column B where the values in column A are at least 60.

Using Wildcards

COUNTIF, COUNTIFS, SUMIF, and SUMIFS can also work with wildcard characters when dealing with text.

The asterisk * represents any number of characters.

For example:

=COUNTIF(A2:A20,"App*")

can count text beginning with App, such as Apple or Application.

The question mark ? represents a single character.

For example:

=COUNTIF(A2:A20,"A?un")

can match four-character text where the second character can vary.

Wildcards are useful when the exact text is not known or when several variations of a word need to be included.

Practical Applications

These four functions have many practical applications in Excel.

Student Records

To count students who scored 60 or more:

=COUNTIF(B2:B100,">=60")

To calculate the total marks of students who scored 60 or more:

=SUMIF(B2:B100,">=60",B2:B100)

Employee Records

To count active employees in the IT department:

=COUNTIFS(B2:B100,"IT",C2:C100,"Active")

To calculate their total salary:

=SUMIFS(D2:D100,B2:B100,"IT",C2:C100,"Active")

Sales Reports

To count sales transactions from the South region:

=COUNTIF(B2:B100,"South")

To calculate total South-region sales:

=SUMIF(B2:B100,"South",D2:D100)

To calculate sales for a particular product in a particular region:

=SUMIFS(D2:D100,A2:A100,"Laptop",B2:B100,"South")

Common Mistakes

A common mistake with COUNTIFS and SUMIFS is providing ranges of different sizes. The criteria ranges should normally correspond correctly to the data being evaluated.

Another common mistake is confusing the argument order of SUMIF and SUMIFS.

Remember:

SUMIF:
=SUMIF(range,criteria,sum_range)

SUMIFS:
=SUMIFS(sum_range,criteria_range,criteria,...)

Users should also be careful when entering text criteria. Text conditions generally need quotation marks when written directly in a formula.

For example:

=COUNTIF(A2:A20,"Sales")

is correct, while:

=COUNTIF(A2:A20,Sales)

will not normally work as intended because Excel may interpret Sales as a name rather than text.

Summary

COUNTIF, COUNTIFS, SUMIF, and SUMIFS are important Excel functions for analyzing structured data.

COUNTIF counts records meeting one condition.

COUNTIFS counts records meeting multiple conditions.

SUMIF adds values meeting one condition.

SUMIFS adds values meeting multiple conditions.

Together, these functions make it possible to create dynamic reports without manually filtering and calculating data. They are particularly valuable when working with large datasets because a single formula can automatically calculate totals or counts whenever the underlying data changes.