Excel-COUNTIF, COUNTIFS, SUMIF and SUMIFS Practice Questions with Solutions

Introduction

COUNTIF, COUNTIFS, SUMIF, and SUMIFS are important Excel functions for working with data based on conditions. They are useful for counting records, adding values that match specific criteria, and analyzing tables. In this chapter, you will practice these functions through 10 practical questions, starting with simple single-condition examples and gradually moving to multiple-condition problems. Excel-COUNTIF, COUNTIFS, SUMIF and SUMIFS practice questions with solutions to help you understand the concepts.

Q1. Count Students Who Scored More Than 80

Problem Statement

You have a list of student marks. Use COUNTIF to count how many students scored more than 80.

Sample Data

Excel Formula

=COUNTIF(B2:B7,">80")

Output

3

Explanation

COUNTIF counts cells that satisfy one condition.

B2:B7 → Range to check
">80" → Condition

The students scoring above 80 are Priya, Amit, and Karan.

Concept Covered

• COUNTIF
• Comparison criteria
• Counting based on a condition

Q2. Count Employees From a Particular Department

Problem Statement

Use COUNTIF to count how many employees belong to the IT department.

Sample Data

Excel Formula

=COUNTIF(B2:B7,"IT")

Output

3

Explanation

The formula checks the Department column and counts cells containing IT.

COUNTIF(range, criteria)

Here:

Range = B2:B7
Criteria = "IT"

Concept Covered

• Text criteria
• COUNTIF
• Department-wise counting

Q3. Count Products With Low Stock

Problem Statement

A store maintains product stock. Use COUNTIF to count products where stock is less than 10.

Sample Data

Excel Formula

=COUNTIF(B2:B7,"<10")

Output

3

Explanation

The formula counts values smaller than 10.

The matching products are:

Mouse → 7
USB Cable → 5
Headphone → 8

Concept Covered

• COUNTIF
• Less-than criteria
• Inventory analysis

Q4. Count Sales From a Specific City and Product

Problem Statement

Use COUNTIFS to count how many laptop sales were made in Delhi.

Sample Data

Excel Formula

=COUNTIFS(B2:B7,"Delhi",C2:C7,"Laptop")

Output

3

Explanation

COUNTIFS allows multiple conditions.

The formula checks:

City = Delhi
AND
Product = Laptop

Only rows satisfying both conditions are counted.

Concept Covered

• COUNTIFS
• Multiple criteria
• AND logic

Q5. Count Employees With Salary Above 50,000 in IT

Problem Statement

Use COUNTIFS to count employees who:

• Work in the IT department
• Have a salary greater than ₹50,000

Sample Data

Excel Formula

=COUNTIFS(B2:B7,"IT",C2:C7,">50000")

Output

3

Explanation

Both conditions must be true.

Department = IT
Salary > 50000

Matching employees:

Rahul
Neha
Simran

Concept Covered

• COUNTIFS
• Multiple conditions
• Numeric criteria
• AND logic

Q6. Calculate Total Sales for a Particular Product

Problem Statement

Use SUMIF to calculate the total sales of all laptops.

Sample Data

Excel Formula

=SUMIF(A2:A7,"Laptop",B2:B7)

Output

173000

Explanation

SUMIF adds values based on one condition.

The formula checks column A for Laptop and adds the corresponding values from column B.

55000 + 60000 + 58000 = 173000

Concept Covered

• SUMIF
• Conditional addition
• Text criteria

Q7. Calculate Total Sales for Delhi

Problem Statement

Use SUMIF to calculate the total sales generated in Delhi.

Sample Data

Excel Formula

=SUMIF(B2:B7,"Delhi",C2:C7)

Output

78000

Explanation

The formula finds rows where the city is Delhi:

25000
18000
35000

Then adds them:

25000 + 18000 + 35000 = 78000

Concept Covered

• SUMIF
• Criteria range
• Sum range
• Sales analysis

Q8. Calculate IT Department Sales Above ₹50,000

Problem Statement

Use SUMIFS to calculate the total sales for the IT department where each sale is greater than ₹50,000.

Sample Data

Excel Formula

=SUMIFS(C2:C7,B2:B7,"IT",C2:C7,">50000")

Output

177000

Explanation

SUMIFS uses multiple conditions.

The formula checks:

Department = IT
Sales > 50000

Matching sales are:

55000
70000
52000

Therefore:

55000 + 70000 + 52000 = 177000

Concept Covered

• SUMIFS
• Multiple criteria
• Conditional addition
• AND logic

Q9. Calculate Total Laptop Sales in Delhi

Problem Statement

Use SUMIFS to calculate total laptop sales made in Delhi.

Sample Data

Excel Formula

=SUMIFS(D2:D7,B2:B7,"Delhi",C2:C7,"Laptop")

Output

170000

Explanation

The formula uses two conditions:

City = Delhi
Product = Laptop

The matching sales are:

55000
58000
57000

Therefore:

55000 + 58000 + 57000 = 170000

Concept Covered

• SUMIFS
• Multiple conditions
• Sales analysis
• Text criteria

Q10. Build a Sales Summary Using COUNTIFS and SUMIFS in Excel

Problem Statement

You have sales data for different cities and products. Calculate:

1. Number of laptop sales in Delhi
2. Total laptop sales in Delhi
3. Number of mouse sales in Mumbai
4. Total mouse sales in Mumbai

Sample Data

1. Count Laptop Sales in Delhi

=COUNTIFS(B2:B9,"Delhi",C2:C9,"Laptop")

Output:

3

2. Total Laptop Sales in Delhi

=SUMIFS(D2:D9,B2:B9,"Delhi",C2:C9,"Laptop")

Output:

175000

3. Count Mouse Sales in Mumbai

=COUNTIFS(B2:B9,"Mumbai",C2:C9,"Mouse")

Output:

3

4. Total Mouse Sales in Mumbai

=SUMIFS(D2:D9,B2:B9,"Mumbai",C2:C9,"Mouse")

Output:

5000

Explanation

This question combines the four functions learned in this chapter.

COUNTIF   → Count using one condition
COUNTIFS  → Count using multiple conditions
SUMIF     → Add using one condition
SUMIFS    → Add using multiple conditions

This type of analysis is commonly useful when working with sales, employee, student, inventory, or customer data.

COUNTIF vs COUNTIFS vs SUMIF vs SUMIFS

Basic Syntax

=COUNTIF(range, criteria)

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

=SUMIF(range, criteria, sum_range)

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

Important Criteria Examples

You can use different types of criteria.

Key Takeaways

• COUNTIF counts cells based on one condition.
• COUNTIFS counts cells based on multiple conditions.
• SUMIF adds values based on one condition.
• SUMIFS adds values based on multiple conditions.
• COUNTIFS and SUMIFS are useful when you need to apply more than one condition.
• Text criteria should normally be placed inside quotation marks.
• Comparison criteria such as ">50000" also use quotation marks.
• SUMIF and SUMIFS are especially useful for sales and financial analysis.
• COUNTIF and COUNTIFS are useful for counting records, employees, products, students, and transactions.
• Understanding the difference between range, criteria_range, and sum_range is important for writing correct formulas.

FAQs

1. What is the difference between COUNTIF and COUNTIFS in Excel?

COUNTIF works with one condition, while COUNTIFS allows you to count records using multiple conditions.

2. What is the difference between SUMIF and SUMIFS?

SUMIF adds values based on one condition. SUMIFS can add values based on multiple conditions.

3. Can COUNTIF count text in Excel?

Yes. For example:

=COUNTIF(A2:A20,"IT")

counts cells containing IT.

4. Can COUNTIFS use more than two conditions?

Yes. COUNTIFS can use multiple criteria ranges and criteria pairs, provided the ranges are set up correctly.

5. Why do I need quotation marks around ">50000"?

Excel treats >50000 as a comparison criterion. When comparison operators are used in criteria, the complete criterion is generally written as text, such as:

">50000"

6. Can SUMIFS add values based on text conditions?

Yes. For example:

=SUMIFS(C2:C20,A2:A20,"Delhi")

can add values from column C where column A contains Delhi.

7. Which function should I use to count sales records matching two conditions?

Use COUNTIFS. For example, if you want to count laptop sales made in Delhi:

=COUNTIFS(B2:B20,"Delhi",C2:C20,"Laptop")

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar