countifs

 The COUNTIFS function in Excel is a powerful tool that allows you to count the number of cells across multiple ranges that meet one or more criteria. It's particularly useful for analyzing data sets where multiple conditions need to be evaluated simultaneously.

Syntax:

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

• criteria_range1: The first range in which to evaluate the associated criteria.
• criteria1: The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted.
• criteria_range2, criteria2, ...: Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Important: Each additional range must have the same number of rows and columns as the criteria_range1 argument.

Examples:

1. Counting Sales Above a Certain Amount in a Specific Region

   Data:

   Region	Sales
   North	500
   South	700
   East	300
   West	700
   North	600

   Formula: To count the number of sales in the "North" region that are greater than 400:

   
   =COUNTIFS(A2:A6, "North", B2:B6, ">400")
   

   Result: 2 (There are two sales in the North region exceeding 400.)

2. Counting Products Meeting Multiple Criteria

   Data:

   Product	Type	Quantity
   Apple	Fruit	50
   Carrot	Veg	30
   Banana	Fruit	70
   Lettuce	Veg	20
   Apple	Fruit	60

   Formula: To count the number of "Fruit" products with a quantity greater than 50:

   
   =COUNTIFS(B2:B6, "Fruit", C2:C6, ">50")
   

   Result: 2 (There are two fruit products with quantities greater than 50.)

3. Counting Dates Within a Specific Range

   Data:

   Date	Event
   01/01/2025	Conference
   15/01/2025	Meeting
   20/01/2025	Workshop
   25/01/2025	Seminar
   30/01/2025	Conference

   Formula: To count the number of events between 10/01/2025 and 20/01/2025:

   
   =COUNTIFS(A2:A6, ">=10/01/2025", A2:A6, "<=20/01/2025")
   

   Result: 2 (There are two events between these dates.)

4. Counting Text Entries with Wildcards

   Data:

   Name	Department
   John Smith	Sales
   Jane Doe	Marketing
   Jake Long	Sales
   Jenna Ray	HR
   James Bond	Sales

   Formula: To count the number of employees in the Sales department whose names start with "J":

   
   =COUNTIFS(B2:B6, "Sales", A2:A6, "J*")
   

   Result: 3 (There are three employees in Sales whose names start with "J.")

5. Counting Entries with Multiple Criteria Across Different Columns

   Data:

   Employee	Department	Status
   Alice	HR	Active
   Bob	IT	Inactive
   Charlie	HR	Active
   David	IT	Active
   Eve	HR	Inactive

   Formula: To count the number of active employees in the HR department:

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

   Result: 2 (There are two active employees in the HR department.)

6. Counting Entries with Numeric and Text Criteria

   Data:

   Item	Category	Price
   Laptop	Tech	1200
   Chair	Furniture	150
   Desk	Furniture	300
   Monitor	Tech	400
   Mouse	Tech	25

   Formula: To count the number of Tech items priced above 500:

   
   =COUNTIFS(B2:B6, "Tech", C2:C6, ">500")
   

   Result: 1 (Only the Laptop meets both criteria.)

7. Counting Entries with Not Equal Criteria

   Data:

   Student	Grade
   Alice	A
   Bob	B
   Charlie	A
   David	C
   Eve	B

   Formula: To count the number of students who did not receive a grade of "A":

   
   =COUNTIFS(B

8. Counting Entries with Numeric and Text Criteria

Data:

Item	Category	Price
Laptop	Tech	1200
Chair	Furniture	150
Desk	Furniture	300
Monitor	Tech	400
Mouse	Tech	25

1. Formula: To count the number of "Tech" items priced above 500:

=COUNTIFS(B2:B6, "Tech", C2:C6, ">500")
Result: 1 (Only the Laptop meets both criteria.)
Counting Entries with Not Equal Criteria
Data:

Student	Grade
Alice	A
Bob	B
Charlie	A
David	C
Eve	B

1. Formula: To count the number of students who did not receive a grade of "A":

=COUNTIFS(B2:B6, "<>A")
Result: 3 (Bob, David, and Eve did not receive an "A".)
Counting Blank and Non-Blank Cells

Data:

1. Task	Due Date
   Task 1	01/02/2025
   Task 2
   Task 3	05/02/2025
   Task 4
   Task 5	10/02/2025

Formulas:
To count the number of tasks with a due date:

=COUNTIFS(B2:B6, "<>")
Result: 3 (There are three tasks with due dates.)
To count the number of tasks without a due date:

=COUNTIFS(B2:B6, "")
Result: 2 (There are two tasks without due dates.)
Counting Entries with Wildcards

Data:

1. Filename	Size (MB)
   report_final	2
   report_draft	1.5
   summary_final	1
   data_final	3
   notes_draft	0.5

Formula: To count the number of files ending with "_final":

=COUNTIFS(A2:A6, "*_final")
Result: 3 (There are three files ending with "_final".)
Counting Entries Based on Date Criteria

Data:

Employee	Hire Date
John	01/01/2020
Jane	15/03/2021
Jake	20/07/2022
Jenna	25/10/2023
James	30/12/2024

Formula: To count the number of employees hired after January 1, 2022:

=COUNTIFS(B2:B6, ">01/01/2022")

Result: 3 (Jake, Jenna, and James were hired after this date.)

These examples illustrate how the COUNTIFS function can be applied to various scenarios, including numerical comparisons, text matching with wildcards, date evaluations, and handling blank or non-blank cells. By adjusting the criteria and ranges, you can tailor the COUNTIFS function to meet specific data analysis need