SUMIF and COUNTIF

Lesson 8: SUMIF and COUNTIF

In data analysis, analysts often need to answer questions such as:

• How much did each salesperson sell?
• How many students passed?
• How many products belong to a particular category?
• What is the total revenue from a specific region?
• How many transactions were above a certain amount?

Instead of manually filtering and calculating these values, Excel provides
conditional functions such as SUMIF and
COUNTIF.

These functions allow analysts to summarize information based on criteria.
They are extremely useful when working with business, financial, student,
sales, inventory, and operational datasets.

1. Learning Objectives

By the end of this lesson, you should be able to:

• Explain the purpose of SUMIF.
• Explain the purpose of COUNTIF.
• Understand criteria-based analysis.
• Write SUMIF formulas correctly.
• Write COUNTIF formulas correctly.
• Use text criteria.
• Use numeric criteria.
• Use comparison operators inside criteria.
• Use cell references as criteria.
• Work with dates and date criteria.
• Use wildcards such as * and ?.
• Understand the difference between SUMIF and SUMIFS.
• Understand the difference between COUNTIF and COUNTIFS.
• Combine conditional functions with other Excel functions.
• Apply SUMIF and COUNTIF to practical datasets.

2. What Is Conditional Summarization?

Conditional summarization means calculating information only when a specific
condition is satisfied.

For example, suppose a sales dataset contains:

You may want to calculate only the sales generated in the South region.

SUMIF can perform this calculation automatically.

3. What Is SUMIF?

SUMIF adds numbers that meet a specified condition.

The basic syntax is:

=SUMIF(range, criteria, sum_range)

There are three main arguments:

1. Range: The cells Excel checks against the condition.
2. Criteria: The condition that must be satisfied.
3. Sum_range: The cells containing the numbers to add.

4. Basic SUMIF Example

Suppose column B contains regions and column D contains sales.

To calculate total sales for the South region:

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

Excel checks B2:B100 for the word South and adds the corresponding values
from D2:D100.

5. SUMIF Structure

Think about SUMIF as:

Check this range
       ↓
Find this condition
       ↓
Add corresponding values

For example:

=SUMIF(B2:B10,"South",D2:D10)

The region column is checked, while the sales column is summed.

6. SUMIF With Text Criteria

Text criteria are placed inside quotation marks.

Example:

=SUMIF(C2:C100,"Laptop",D2:D100)

This adds sales only where the product is Laptop.

Other examples:

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

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

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

7. SUMIF With a Cell Reference

Instead of typing the criteria directly, you can store it in another cell.

If F2 contains South:

=SUMIF(B2:B100,F2,D2:D100)

This makes the spreadsheet more flexible because changing F2 changes the
result.

8. SUMIF With Numeric Criteria

SUMIF can also evaluate numbers.

Suppose column B contains transaction values and you want to add all values
greater than 100,000.

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

This adds only values greater than 100,000.

9. SUMIF With Greater Than or Equal To

Example:

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

This includes values equal to 50,000 as well as values above 50,000.

10. SUMIF With Less Than

Example:

=SUMIF(B2:B100,"<50000",B2:B100)

This adds values below 50,000.

11. SUMIF With Not Equal To

The not-equal operator is <>.

Example:

=SUMIF(B2:B100,"<>0",B2:B100)

This adds values that are not zero.

12. SUMIF and Products

Suppose:

• Column A = Product
• Column B = Quantity
• Column C = Revenue

To calculate revenue generated by laptops:

=SUMIF(A2:A100,"Laptop",C2:C100)

This can be used to build product performance reports.

13. SUMIF and Student Data

Suppose a training center has a student dataset with:

• Student Name
• Course
• Payment Status
• Amount Paid

To calculate the amount paid by students enrolled in Data Analysis:

=SUMIF(B2:B100,"Data Analysis",D2:D100)

This can help management understand revenue by course.

14. What Is COUNTIF?

COUNTIF counts the number of cells that satisfy a specified
condition.

The basic syntax is:

=COUNTIF(range, criteria)

There are two main arguments:

1. Range: The cells to evaluate.
2. Criteria: The condition to count.

15. Basic COUNTIF Example

Suppose column B contains student results.

To count students who passed:

=COUNTIF(B2:B100,"Pass")

Excel counts every cell containing Pass.

16. COUNTIF With Text

Examples:

=COUNTIF(A2:A100,"Lagos")

=COUNTIF(B2:B100,"Completed")

=COUNTIF(C2:C100,"Laptop")

These formulas count occurrences of specific text values.

17. COUNTIF With Numbers

COUNTIF can count values based on numeric conditions.

For example, count scores of 50 or more:

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

Count values below 50:

=COUNTIF(B2:B100,"<50")

Count values exactly equal to 100:

=COUNTIF(B2:B100,100)

18. COUNTIF With a Cell Reference

Suppose F2 contains the criteria.

=COUNTIF(B2:B100,F2)

This counts values matching whatever criteria is stored in F2.

19. COUNTIF With Comparison Operators and Cell References

When combining a comparison operator with a cell reference, use the
concatenation operator &.

Suppose F2 contains 50.

Use:

=COUNTIF(B2:B100,">="&F2)

This counts values greater than or equal to the value in F2.

20. COUNTIF for Pass and Fail Analysis

Suppose a class has 100 students.

To count passes:

=COUNTIF(C2:C101,"Pass")

To count failures:

=COUNTIF(C2:C101,"Fail")

This makes it easy to build a simple performance summary.

21. COUNTIF for Attendance

Suppose column D contains attendance percentages.

To count students with attendance of at least 75%:

=COUNTIF(D2:D100,">=75%")

This can be useful for determining how many students meet an attendance
requirement.

22. COUNTIF for Sales Performance

Suppose column E contains sales amounts.

Count sales transactions above 100,000:

=COUNTIF(E2:E100,">100000")

Count transactions below 50,000:

=COUNTIF(E2:E100,"<50000")

23. COUNTIF With Dates

COUNTIF can also work with dates.

For example, if column A contains dates, you can count records occurring
after a particular date.

=COUNTIF(A2:A100,">=01/01/2026")

For more reliable date-based analysis, it is often better to reference a
cell containing a real Excel date.

For example, if F2 contains the date:

=COUNTIF(A2:A100,">="&F2)

24. SUMIF With Dates

SUMIF can also summarize values based on dates.

Suppose:

• Column A = Transaction Date
• Column B = Revenue

To sum revenue from transactions on or after a specified date in F2:

=SUMIF(A2:A100,">="&F2,B2:B100)

25. Wildcards in COUNTIF and SUMIF

Excel supports wildcard characters for text criteria.

Asterisk (*)

The asterisk represents any number of characters.

Example:

=COUNTIF(A2:A100,"*Tech*")

This can count cells containing the word Tech anywhere within the text.

Question Mark (?)

The question mark represents a single character.

Example:

=COUNTIF(A2:A100,"A?e")

This can match three-character values where the middle character can vary.

26. Counting Blank Cells

COUNTIF can also be used to count blank cells.

=COUNTIF(A2:A100,"")

This can help identify missing information.

To count cells that are not blank:

=COUNTIF(A2:A100,"<>")

27. SUMIF vs COUNTIF

For example:

=COUNTIF(B2:B100,"South")

counts the number of South records.

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

adds the corresponding sales for South records.

28. SUMIFS

When you need to apply more than one condition to a sum, Excel provides
SUMIFS.

Example:

=SUMIFS(D2:D100,B2:B100,"South",C2:C100,"Laptop")

This calculates sales where:

• Region is South.
• Product is Laptop.

SUMIFS is an important extension of SUMIF.

29. COUNTIFS

COUNTIFS counts records that satisfy multiple conditions.

Example:

=COUNTIFS(B2:B100,"South",C2:C100,"Laptop")

This counts records where the region is South and the product is Laptop.

30. SUMIF and COUNTIF With Excel Tables

Excel Tables make formulas easier to understand because they can use
structured references.

For example, a table named SalesData might contain:

• Region
• Product
• Sales

A formula may look like:

=SUMIF(SalesData[Region],"South",SalesData[Sales])

Structured references can make analytical spreadsheets easier to maintain.

31. Building a Sales Summary

Suppose your dataset contains:

To calculate South region sales:

=SUMIF(B2:B5,"South",D2:D5)

To count South region transactions:

=COUNTIF(B2:B5,"South")

These two formulas answer different but related analytical questions.

32. Data Quality Applications

COUNTIF can be used to identify potential data-quality problems.

For example, count records containing "N/A":

=COUNTIF(A2:A100,"N/A")

Count negative values:

=COUNTIF(B2:B100,"<0")

Count records that have not been completed:

=COUNTIF(C2:C100,"Pending")

These calculations can help analysts detect problems before performing
deeper analysis.

33. Common Mistakes

Using the wrong range

The criteria range must correspond correctly with the sum range.

Forgetting quotation marks

Text criteria require quotation marks.

Incorrect comparison syntax

For example:

>=50

must normally be supplied as a criteria string when typed directly into
SUMIF or COUNTIF:

">=50"

Using the wrong function

Use COUNTIF when you need a count and SUMIF when you need a total.

Including the wrong sum range

The sum range should contain the numeric values you actually want to add.

Mixing text and numbers

Make sure numeric columns contain actual numbers rather than numbers stored
as text.

34. Practical Exercise

Create a sales dataset containing at least 20 records with:

• Salesperson
• Region
• Product
• Quantity
• Sales Amount
• Payment Status

Then perform the following analysis:

1. Calculate total sales for the South region.
2. Calculate total sales for the North region.
3. Count the number of South region transactions.
4. Count the number of Laptop transactions.
5. Count transactions above ₦100,000.
6. Count unpaid transactions.
7. Calculate total sales for Laptop products.
8. Count records with missing payment status.
9. Use a cell reference as a SUMIF criterion.
10. Use SUMIFS to calculate sales for a region and product combination.
11. Use COUNTIFS to count transactions matching multiple conditions.

35. Knowledge Check

1. What is SUMIF used for?
2. What is COUNTIF used for?
3. What are the three arguments of SUMIF?
4. What are the two main arguments of COUNTIF?
5. How do you sum sales for one region?
6. How do you count records greater than 50?
7. How can a cell reference be used as a criterion?
8. What does the asterisk wildcard represent?
9. What is the difference between SUMIF and SUMIFS?
10. What is the difference between COUNTIF and COUNTIFS?
11. How can COUNTIF help with data-quality analysis?

36. Key Terms

• SUMIF: Adds values that satisfy a condition.
• COUNTIF: Counts cells that satisfy a condition.
• Criteria: The condition used to determine which records are included.
• Criteria Range: The cells Excel evaluates.
• Sum Range: The cells containing values to add.
• SUMIFS: Adds values based on multiple conditions.
• COUNTIFS: Counts records based on multiple conditions.
• Wildcard: Special character used for flexible text matching.
• Structured Reference: Excel Table reference that uses table and column names.
• Conditional Summarization: Summarizing data according to defined conditions.

37. Key Takeaways

• SUMIF adds values that meet a condition.
• COUNTIF counts cells that meet a condition.
• Text criteria normally require quotation marks.
• Comparison operators can be used for numeric analysis.
• Cell references make criteria more flexible.
• SUMIFS handles multiple conditions for summation.
• COUNTIFS handles multiple conditions for counting.
• Wildcards make text searches more flexible.
• SUMIF and COUNTIF are useful for business and student datasets.
• These functions are powerful tools for conditional data summarization.

38. Lesson Summary

SUMIF and COUNTIF are important Excel functions for data analysis because
they allow analysts to summarize information according to specific
conditions.

SUMIF is used when you need to calculate a total, while COUNTIF is used when
you need to determine how many records satisfy a condition.

When multiple conditions are required, SUMIFS and COUNTIFS provide more
advanced options.

Mastering these functions prepares you for more advanced Excel analysis,
including lookup functions, tables, PivotTables, and dashboards.

Next Lesson

In the next lesson, we will learn about
Lookup Functions and how Excel can be used to find and
retrieve information from datasets.