What is the COUNTIF function in excel?
The COUNTIF function in Excel is one of the most powerful and useful tools. You can use the COUNTIF function to count the number of cells based on a specific condition. It can quickly and easily count the number based on the conditions you provide. COUNTIF conditions can be text, numbers, partial text, or creatively combined with other formulas and used as criteria across multiple columns. COUNTIF doesn’t just perform a simple count; it does more than that. It speeds up your work, saving you a lot of time by doing it manually. Instead of manually scanning rows to see if an item meets your criteria, COUNTIF does it instantly, saving time and reducing errors.
When you are working with large data in Excel, there are many times you need to count specific values—like how many times a name appears in a list or how many products were sold above a certain quantity. In these conditions, you can use the COUNTIF function in excel, which is a very useful function of Excel.
In simple terms, the COUNTIF function in Excel helps you count the number of cells in a range that meet a single condition (also called a criterion). It’s part of Excel’s statistical functions and is widely used in data analysis, reporting, and automation.
We’ll explain the COUNTIF function in detail, how you can use it, and, based on practical situations, you can practice some real examples step-by-step where COUNTIF can save you time and manual work.
How to write COUNTIF formula in excel?
Syntax:
=COUNTIF(range,criteria)
COUNTIF function in Excel: (Step by Step Tutorial)
Formula:
=COUNTIF(P5:P30,”PEN”)
Explanation:
Range: P5:P30, the range where we want to find matches
Criteria: “PEN” This function will count how many times the word PEN repeated in the selected range
Result: 4 the word Pen appears 4 times in the selected column
Why It Works
The COUNTIF function in Excel examines each cell in the range and counts only those that match the specified condition exactly provided by you.
Pro Tip:
COUNTIF is not case-sensitive. Whether the cell contains Pen, pen, or PEN, all will be counted equally.
1. Using COUNTIFS function in Excel with multiple criteria in same column
Formula:
=SUM(COUNTIFS(R5:R16, {“PEN”,”NOTEBOOK”,”GLUE”}))
Explanation:
Range: (R5:R16)
The COUNTIFS function finds the Items column (R5:R16) for each item in the array: pens, notebooks, and glue. After this, the SUM function adds up all the counts.
Result: 11 (Total occurrences of Pen, Notebook, and Glue)
Why It Works:
The COUNTIF function in Excel also works with array constants. Multiple values can be calculated at once because of an array constant inside the function. This functions similarly to three separate COUNTIFS formulas working together.
An array constant inside the function allows multiple values to be calculated at once. This works like three separate COUNTIFS formulas running together.
Pro Tip:
To count multiple values without using separate formulas, use the array constants with COUNTIFS.
2. How to use COUNTIFS function in excel with multiple criteria in multiple column (AND Logic)
Formula:
=COUNTIFS(Q5:Q16,Q18,R5:R16,Q19,S5:S16,Q20)
Explanation:
Range: Q5:Q16, R5:R16, S5:S16
Criteria Applied:
Status is Complete (cell Q18)
Item is Pen (cell Q19)
Quantity is greater than 50 (cell Q20)
What it does:
Counts how many orders match all three conditions:
Result: 2 orders matched all three conditions.
Why It Works:
COUNTIFS can check more than one range at a time with multiple conditions at once.
Each condition must match in the corresponding row.
Pro Tip:
You can use COUNTIF function in excel for filtering real business data sales reports, inventory checks, orders pending, etc.
3. How to Use COUNTIF + COUNTIF in Excel (OR Logic)
Formula:
=COUNTIFS(Q5:Q16,Q18,R5:R16,Q19)+COUNTIFS(Q5:Q16,Q18,R5:R16,R19)
Explanation:
Counts Pending orders for:
Pen (Q19)
Glue (R19)
What it does:
Count the number of Pending orders where the item is either Pen or Glue.
Result: 3 matching orders
Why It Works:
Combines the results of the two separate COUNTIFS, which works like an OR condition. COUNTIFS does not support OR logic within the same criteria range. By using two separate COUNTIFS formulas and combining them, we simulate an OR condition.
Pro Tip:
COUNTIFS uses AND logic; use + to simulate OR logic.
4. How to Check If a Cell Contains Partial Text Using COUNTIF function in excel
Example 1: COUNTIF Contains Partial Text
Formula:
=IF(COUNTIF(P5,”*AA*”),”YES”,”NO”)
Explanation:
The COUNTIF checks the cell P5 contains the substring ‘AA’. The asterisks (*) are wildcards, meaning anything can appear before or after ‘AA’.
What it does: Checks if a cell in column P contains “AA” anywhere in the text.
Condition apply any text before or after “AA” is allowed.
Result: If ‘AA’ is found, it returns YES otherwise NO.
Why It Works:
COUNTIF with wildcards helps detect partial matches inside a text string. This is useful when IDs, names, or codes follow a pattern.
Example 2: COUNTIF Start With, End With, and Contains
Formula:
=COUNTIF(P5:P16,”NOTE*”) → Finds entries starting with the word “NOTE”.
=COUNTIF(P5:P16,”*B”) → Finds entries ending with the letter “B”.
=COUNTIF(P5:P16,”*002*”) → Finds entries containing “002” anywhere in the text.
Explanation:
Use the COUNTIF function in Excel with wildcards to identify values that match certain patterns.
This method is ideal for identifying items with specific tags, structured codes, or patterns inside long product names in your dataset.
What it does:
NOTE* finds any cell starting with ‘NOTE’
*B finds any cell ending with ‘B’
*002* finds any cell that contains ‘002’ anywhere inside
Result:
Counts how many cells:
Starts with “NOTE”: 3 matches
Ends with “B”: 6 matches
Contains “002”: 4 matches
Pro Tip:
Great for cleaning and analyzing messy data with IDs, tags, or part numbers.
5. COUNTIF with SUMPRODUCT for Unique Count
Formula:
=SUMPRODUCT(1/COUNTIF(P5:P30,P5:P30))
Explanation:
COUNTIF(P5:P30, P5:P30) counts how many times each item appears in the range.
What it Does:
Count unique items in the range P5 to P30.
Powerful for deleting duplicates of lists.
This advanced formula calculates how many unique values exist in a range.
Result:
6 unique items in the list (Pen, Glue, Notebook, Eraser, Calculator, Sticky Notes) Even if Pen appears 6 times, it will count it once.
Why This Works:
1/COUNTIF(…) converts those counts into fractions.
Example: If “Pen” appears 5 times → 1/5 = 0.2
SUMPRODUCT(…) adds all those fractions together.
Summary:
| Use Case | Formula Example | Key Tip |
| Count exact match | =COUNTIF(P5:P30, “Pen”) | Not case-sensitive |
| Count multiple items | =SUM(COUNTIFS(..,{“Pen”,”Notebook”})) | Use array constants |
| Count with multiple conditions | =COUNTIFS(…, …, …, …) | All conditions must be met (AND) |
| Use OR logic with COUNTIFS | =COUNTIFS(…)+COUNTIFS(…) | Combine using + symbol |
| Match partial text | =COUNTIF(P5, “*AA*”) | Wildcards like * make it flexible |
| Partial Match | =IF(COUNTIF(cell,”*text*”),”YES”,”NO”) | Check if cell contains part of a string |
| Starts With | =COUNTIF(range,”text*”) | Count values that begin with specific text |
| Ends With | =COUNTIF(range,”*text”) | Count values that end with specific text |
| Contains | =COUNTIF(range,”*text*”) | Count values that contain a substring |
| Unique Count | =SUMPRODUCT(1/COUNTIF(range,range)) | Count unique values in a range |
Conclusion:
You can use the COUNTIF function in Excel to analyze sales records, track inventory, or process survey data. COUNTIF makes your life easier by getting rid of manual counting. You can use a single criterion for multiple complex conditions; this function simplifies data analysis and saves time.
When should you use the COUNTIF function in Excel?
You can use the COUNTIF function in Excel in different ways, including counting the number of times a value occurs in a list, tracking attendance by marking individuals as “present” or “absent,” and even measuring the frequency of survey responses.
Remember:
Use COUNTIF with SUMPRODUCT to calculate unique values in a column
Combine COUNTIF with IF for conditional logic
Use wildcards (* and ?) to find partial matches