COUNTIF Formula Excel – Count Cells with Criteria [Complete Guide]

It was a Friday afternoon at a textile company in Surat. Amit, the HR manager, had just received a message from his director: “How many employees attended the training session last month? How many are from the Sales department? And out of those, how many are from the North region?” Three questions. 847 rows of data. Amit opened his spreadsheet, started scrolling, and began counting manually. By 5:30 PM, he had not finished. He stayed back two hours.

What Amit did not know was that all three answers were waiting for him – in a single formula that takes 10 seconds to type. That formula is COUNTIF. This guide will make sure you never count manually again.

COUNTIF is one of the most practical formulas in all of Excel. It does one thing perfectly: it counts how many cells in a range meet a specific condition – a criteria you define. It does not matter if your list has 10 rows or 10,000 rows. COUNTIF gives you the answer in the same amount of time: instantly.

This guide is written in plain, friendly language. You will see real stories from real office situations, step-by-step examples from HR, Sales, Finance, and Operations, and clear tables that show exactly what Excel will display. Whether you are brand new to Excel formulas or you just want to finally understand COUNTIF properly – this guide covers everything.

Part 1: What Is COUNTIF? – The Simplest Explanation Ever

Before looking at any formula, let us understand what COUNTIF actually does – using everyday life.

Imagine you are organising a wedding. You have a guest list of 300 names in a notebook. Your aunt calls and asks: ‘How many guests are from Mumbai?’ You could read every single name and count the Mumbai ones manually. Or – if you are smart – you would use COUNTIF. COUNTIF does exactly what you would do if you had a highlighter and a checklist: it goes through every cell in your range, checks if it matches your condition, and tells you the total count. That is it. No addition. No manual reading. Just a count based on a rule you set.

In Excel, COUNTIF answers questions like these in under one second:

• How many employees are in the Sales department?
• How many invoices are above Rs. 10,000?
• How many students scored below 40?
• How many orders are still Pending?
• How many cells in this column are blank?
• How many products start with the word ‘Pro’?

If any of those questions sound familiar from your own work – COUNTIF is the formula you have been missing.

Part 2: COUNTIF Syntax – Every Part Explained Clearly

COUNTIF Syntax:
=COUNTIF( range, criteria )

Part	What It Means	Plain English	Example
range	The group of cells you want Excel to search through	Which column or range should Excel look at?	A2:A500  or  B2:B100  or  D:D
criteria	The condition – what you are looking for	What should Excel count?	“Sales”  or  “>5000”  or  “Pending”  or  A1
= sign	Every formula in Excel starts with = – never forget it	Tells Excel: this is a formula, not just text	=COUNTIF(…)
Comma	Separates the two arguments in the formula	Do not use a semicolon – use a comma	=COUNTIF(A2:A100, “Sales”)
Quotes for text criteria	Any text or operator+number must be inside double quotes	Numbers alone do not need quotes – text always does	“Approved”  “>100”  “<>”

The Golden Rule of Criteria

If your criteria is a plain number – like 50 – write it without quotes: =COUNTIF(B2:B100, 50). But if your criteria is text – like Sales – or a comparison like >50, wrap it in double quotes: =COUNTIF(A2:A100, “Sales”) or =COUNTIF(B2:B100, “>50”). This single rule prevents 80% of COUNTIF errors.

Part 3: Your First COUNTIF Formula – Step by Step

Amit’s First COUNTIF Moment: Let us go back to Amit. His director asked: how many employees are from the Sales department? Employee names are in column A. Departments are in column B. Amit needs to count how many cells in column B contain the word Sales. Let us build this together.

Step 1 – Identify the Two Parts

Question to Ask Yourself	Amit’s Answer
Which range should Excel search through?	Column B (B2:B500) – that is where department names are
What should it count?	The word Sales – only cells that say exactly Sales

Step 2 – Write the Formula

Cell E2 - Count of Sales Employees:
=COUNTIF(B2:B500, "Sales")

Step 3 – Understand What Excel Does

Excel scans every cell from B2 to B500. For each cell, it asks: does this say Sales? Every time the answer is yes, it adds 1 to a running count. When it reaches B500, it shows you the final total. Whether 3 or 300 cells match – the formula takes the same fraction of a second.

Step 4 – Make It Dynamic with a Cell Reference

Instead of typing the department name directly into the formula, you can put it in a separate cell and reference it. This lets you change the criteria without editing the formula every time.

Dynamic COUNTIF using a cell reference:
=COUNTIF(B2:B500, E1)

-- If E1 contains the word Sales, this counts Sales employees
-- Change E1 to HR, and it instantly counts HR employees
-- No formula editing needed - just change the cell

Always Lock Your Range with $ Signs: If you plan to copy your COUNTIF formula to other cells, lock the range with dollar signs: =COUNTIF($B$2:$B$500, E1). The $ signs freeze the range so it does not shift when you copy the formula down or across. The criteria cell E1 is left unlocked so it can change as needed.

Part 4: COUNTIF with Different Types of Criteria

COUNTIF is incredibly flexible. The criteria – the second argument – can be text, a number, a comparison, a wildcard, a date, or even a blank check. Here is a complete breakdown of every type.

4A – Counting an Exact Text Match

The most common use: count cells that contain a specific word or phrase exactly.

Examples - Exact Text Criteria:
=COUNTIF(B2:B500, "Sales")         -- Count cells that say Sales
=COUNTIF(C2:C300, "Approved")      -- Count approved records
=COUNTIF(D2:D200, "North")         -- Count North region entries
=COUNTIF(E2:E100, "Full-Time")     -- Count full-time employees

COUNTIF is NOT Case-Sensitive: COUNTIF treats Sales, SALES, and sales as the same thing. If you need case-sensitive counting, you must use SUMPRODUCT with EXACT instead. For most everyday office tasks, case-insensitivity is actually helpful.

4B – Counting with Comparison Operators

You can count cells based on number comparisons: greater than, less than, equal to, not equal to. The operator goes inside the criteria, wrapped in quotes.

Examples - Comparison Criteria:
=COUNTIF(C2:C300, ">10000")        -- Count sales above Rs. 10,000
=COUNTIF(C2:C300, ">=10000")       -- Count sales at or above Rs. 10,000
=COUNTIF(C2:C300, "<5000")         -- Count sales below Rs. 5,000
=COUNTIF(D2:D200, "<>0")           -- Count cells that are NOT zero
=COUNTIF(B2:B100, "<>Pending")     -- Count entries that are not Pending

Cell	What We Want to Count	COUNTIF Formula	Answer
E2	Count sales > 10,000	=COUNTIF(C2:C300,”>10000″)	47
E3	Count sales >= 10,000	=COUNTIF(C2:C300,”>=10000″)	53
E4	Count entries not Pending	=COUNTIF(B2:B100,”<>Pending”)	82
E5	Count scores < 40 (fail)	=COUNTIF(D2:D150,”<40″)	18
E6	Count non-zero stock	=COUNTIF(F2:F500,”<>0″)	341

4C – Counting Blank and Non-Blank Cells

One of the most useful COUNTIF tricks is checking for missing or filled data – perfect for data quality checks before submitting reports.

Count Blank and Non-Blank Cells:
=COUNTIF(B2:B500, "")             -- Count EMPTY (blank) cells
=COUNTIF(B2:B500, "<>")           -- Count NON-EMPTY (filled) cells

-- Simpler alternatives:
=COUNTBLANK(B2:B500)              -- Also counts blanks (dedicated function)
=COUNTA(B2:B500)                  -- Counts non-blank cells

The Missing Data Check: Rekha works in admin at a hospital. Every Friday, she needs to confirm that all 150 patient records have a contact number filled in column D. She types =COUNTIF(D2:D151, “”) in an empty cell. If the result is 0, all records are complete. If it shows 7, she knows exactly how many are missing and can filter to find them. What used to take 20 minutes of manual checking now takes 3 seconds.

4D – Counting with Wildcard Characters

Wildcards let you count cells based on partial matches – incredibly useful when values are not exactly the same but follow a pattern.

Wildcard	Meaning	Example Criteria	What It Matches
* (asterisk)	Zero or more of any characters	“Sales*”	Sales, SalesManager, Sales-North, Salesperson
* (asterisk)	Zero or more of any characters	“*Manager”	Sales Manager, HR Manager, Senior Manager
* (asterisk)	Zero or more of any characters	“*Kumar*”	Raj Kumar, Ankit Kumar Sharma, Kumar Singh
? (question mark)	Exactly one character	“A?”	AB, AC, A1, AX – exactly 2 characters starting with A
~* (tilde+asterisk)	A literal asterisk character (not wildcard)	“~*”	Cells that actually contain the * symbol

Wildcard COUNTIF Examples:
=COUNTIF(B2:B500, "Sales*")       -- Cells starting with Sales
=COUNTIF(B2:B500, "*Manager")     -- Cells ending with Manager
=COUNTIF(A2:A300, "*Kumar*")      -- Cells containing Kumar anywhere
=COUNTIF(C2:C200, "Pro*")         -- Products starting with Pro
=COUNTIF(D2:D150, "MH-??")        -- Cells like MH-01, MH-42 (4 chars total)

The Product Search Problem: Vikram manages inventory at a FMCG company. He has 600 product names in column A. His manager asks: how many products belong to the Pro series? The products are named Pro-Wash, Pro-Clean, Pro-Shine, Pro-Guard etc. Vikram types =COUNTIF(A2:A601, “Pro*”) and gets his answer – 47 products – in under 5 seconds. The * wildcard matched every product name that started with Pro, regardless of what came after.

4E – Counting Dates

You can use COUNTIF to count cells with specific dates, or dates before/after a certain point. Dates in criteria must be text strings in a format Excel recognises, or use the DATE function for reliability.

COUNTIF with Dates:
=COUNTIF(D2:D300, "01/15/2024")           -- Exact date match
=COUNTIF(D2:D300, ">"&DATE(2024,1,1))     -- After Jan 1 2024
=COUNTIF(D2:D300, "<"&TODAY())            -- Dates before today (overdue)
=COUNTIF(D2:D300, ">="&DATE(2024,4,1))    -- On or after April 1 2024

Combine Text Operator with DATE using & When combining a comparison operator with a date or cell reference, use the & symbol to join them: =COUNTIF(D2:D300, “>”&DATE(2024,1,1)). The & concatenates the > sign (in quotes) with the date value. This is the safest and most reliable way to write date criteria in COUNTIF.

Part 5: COUNTIFS – Count with Multiple Criteria at Once

Amit’s Three Questions – Answered

Remember Amit from the beginning? His director asked three questions at once: total training attendees, how many from Sales, and how many from Sales in the North region. The third question requires two conditions at the same time – department must be Sales AND region must be North. A basic COUNTIF can only check one condition. This is exactly where COUNTIFS comes in.
COUNTIFS is the multi-condition version of COUNTIF. It works with two, three, four, or more criteria simultaneously – and every single one must be satisfied for a row to be counted.

COUNTIFS Syntax:
=COUNTIFS( range1, criteria1, range2, criteria2, range3, criteria3, ... )

-- You can add as many range-criteria pairs as needed
-- All criteria must match for a row to be counted (AND logic)

Example 1 – Two Criteria: Department AND Region

Answering Amit’s Third Question: Department is in column B. Region is in column C. Amit needs to count rows where B says Sales AND C says North at the same time.

Cell F2 - Sales employees in the North region:
=COUNTIFS(B2:B500, "Sales", C2:C500, "North")

-- Read as: Count rows where column B is Sales
--          AND column C is North
-- Only rows where BOTH match are counted

Cell	What We Want to Count	COUNTIF Formula	Answer
F2	Sales dept + North region	=COUNTIFS(B2:B500,”Sales”,C2:C500,”North”)	23
F3	HR dept + South region	=COUNTIFS(B2:B500,”HR”,C2:C500,”South”)	11
F4	Finance + Active status	=COUNTIFS(B2:B500,”Finance”,D2:D500,”Active”)	34

Example 2 – Number Range: Count Between Two Values

To count values that fall between two numbers – for example, sales between Rs. 5,000 and Rs. 20,000 – use COUNTIFS with two criteria on the same column.

Count values between a range (both criteria on same column):
=COUNTIFS(C2:C300, ">="&5000, C2:C300, "<="&20000)

-- Counts cells in C where value is >= 5000 AND <= 20000
-- Same range, two different criteria - this is the standard technique

Example 3 – Three Criteria: Department + Region + Status

Three-condition COUNTIFS:
=COUNTIFS(B2:B500, "Sales", C2:C500, "North", D2:D500, "Active")

-- Count employees who are:
-- In Sales department  AND  North region  AND  Active status

Use Case	COUNTIFS Formula	What It Counts
Count Sales staff in East who joined after 2022	=COUNTIFS(B2:B500,”Sales”,C2:C500,”East”,E2:E500,”>”&DATE(2022,12,31))	Sales + East + joined after Dec 2022
Orders above 10k from VIP customers that are Pending	=COUNTIFS(D2:D300,”>10000″,E2:E300,”VIP”,F2:F300,”Pending”)	All three conditions must match
Students who passed Math AND passed Science	=COUNTIFS(C2:C100,”>33″,D2:D100,”>33″)	Score >33 in both subjects
Count Jan 2024 invoices above Rs. 50,000	=COUNTIFS(B2:B200,”>=”&DATE(2024,1,1),B2:B200,”<“&DATE(2024,2,1),C2:C200,”>50000″)	Date in Jan 2024 and amount >50k

COUNTIFS Uses AND Logic – Not OR: Every condition in COUNTIFS must be TRUE at the same time for a row to be counted. It does not count rows where ANY condition matches – it counts rows where ALL conditions match. If you need OR logic (count if condition 1 OR condition 2), use two separate COUNTIFs and add them: =COUNTIF(range,crit1)+COUNTIF(range,crit2).

Part 6: COUNTIF vs COUNT vs COUNTA vs COUNTBLANK

Excel has a family of counting functions. Knowing when to use each one saves time and prevents confusion.

Function	What It Counts	Use It When…	Example
COUNT	Only cells with numbers	You want to count how many numeric entries exist	=COUNT(B2:B100)
COUNTA	All non-empty cells (numbers + text + dates)	You want to count all filled cells regardless of type	=COUNTA(B2:B100)
COUNTBLANK	Only empty/blank cells	You want to find how many cells are missing data	=COUNTBLANK(B2:B100)
COUNTIF	Cells matching one condition	You want to count based on a specific rule	=COUNTIF(B2:B100,”Sales”)
COUNTIFS	Cells matching two or more conditions	You need multiple rules – all must match	=COUNTIFS(B:B,”Sales”,C:C,”North”)

Which Function Should I Use?: Neha is an accounts executive. She asks: how many invoices have been paid? She should use =COUNTIF(C2:C500, “Paid”) – one condition on one column. Her colleague Dinesh asks: how many cells in column B have any data at all? He should use =COUNTA(B2:B500). Their manager asks: how many cells are empty in column D? Use =COUNTBLANK(D2:D500). Same data, different questions, different functions.

Part 7: 12 Real-World COUNTIF Formulas – Ready to Use

Here are twelve ready-to-use COUNTIF and COUNTIFS formulas from real office situations. Copy these directly into your work.

HR Department Formulas

Cell	What We Want to Count	COUNTIF Formula	Answer
E2	Count employees in HR dept	=COUNTIF(B2:B500,”HR”)	42
E3	Count Active employees	=COUNTIF(D2:D500,”Active”)	318
E4	Count employees who joined after 2022	=COUNTIF(E2:E500,”>”&DATE(2022,12,31))	87
E5	Count female employees	=COUNTIF(G2:G500,”Female”)	156
E6	Count employees with missing phone number	=COUNTIF(H2:H500,””)	14

Sales & Finance Formulas

Cell	What We Want to Count	COUNTIF Formula	Answer
F2	Count invoices above Rs. 50,000	=COUNTIF(C2:C300,”>50000″)	67
F3	Count orders that are Pending	=COUNTIF(D2:D300,”Pending”)	23
F4	Count sales reps who hit target	=COUNTIF(E2:E100,”Achieved”)	38
F5	Count products below reorder level	=COUNTIF(F2:F200,”<50″)	12
F6	Count overdue invoices (date < today)	=COUNTIF(G2:G300,”<“&TODAY())	9

Operations & School Formulas

Cell	What We Want to Count	COUNTIF Formula	Answer
G2	Count students who failed (score < 35)	=COUNTIF(C2:C150,”<35″)	18
G3	Count North+Sales employees together	=COUNTIFS(B2:B500,”North”,C2:C500,”Sales”)	29

Part 8: Common COUNTIF Mistakes and Exact Fixes

These are the seven mistakes that trip up beginners almost every single time. Knowing them in advance will save you hours of frustration.

Mistake Made	What Was Typed	The Actual Problem	The Correct Formula
Forgot quotes around text criteria	=COUNTIF(B2:B100, Sales)	Excel looks for a named range called Sales – not the word	=COUNTIF(B2:B100, “Sales”)
Forgot quotes around comparison criteria	=COUNTIF(C2:C100, >5000)	Excel throws a #VALUE! or parse error	=COUNTIF(C2:C100, “>5000”)
Different range sizes in COUNTIFS	=COUNTIFS(B2:B100,”Sales”,C2:C200,”North”)	B range has 99 rows, C range has 199 – must match exactly	Make both ranges the same size: B2:B200 and C2:C200
Expected OR logic but got AND	=COUNTIFS(B2:B100,”Sales”,B2:B100,”HR”)	No cell can be both Sales AND HR – result is always 0	Use: =COUNTIF(B2:B100,”Sales”)+COUNTIF(B2:B100,”HR”)
Criteria in wrong data type	=COUNTIF(C2:C100, 5000)	This works for numbers – but fails if column C has numbers stored as text	Check column format. Use TEXT format if needed, or “5000” in quotes
Wildcard with exact number	=COUNTIF(C2:C100, “*50*”)	Wildcards only work with TEXT – not numbers	Use comparison: =COUNTIF(C2:C100, 50) for exact, or SUMPRODUCT for partial number match
Range not locked when copying	=COUNTIF(B2:B100, E1) copied down	Range shifts to B3:B101, B4:B102 etc – wrong results	Lock range: =COUNTIF($B$2:$B$100, E1)

The Most Common COUNTIF Error – Range Size Mismatch in COUNTIFS

In COUNTIFS, every range-criteria pair must cover exactly the same number of rows. =COUNTIFS(B2:B100, “Sales”, C2:C200, “North”) will return a #VALUE! error because B has 99 rows and C has 199. Always double-check that all ranges start and end at the same rows. The safest habit: use full column references like B:B and C:C, or ensure both start and end rows match exactly.

Part 9: Advanced COUNTIF Techniques

Count Unique Values – How Many Distinct Items?

COUNTIF does not have a built-in unique count feature, but you can combine it with SUMPRODUCT to count distinct values in a column.

Count how many unique departments exist in column B:
=SUMPRODUCT(1/COUNTIF(B2:B500, B2:B500))

-- How it works: COUNTIF counts how many times each value appears
-- 1 divided by that count gives a fraction
-- Summing the fractions gives the unique count
-- Note: this formula fails if there are any blank cells in the range

COUNTIF as a Percentage

You can divide your COUNTIF result by COUNTA to get the percentage of rows that match your criteria – great for dashboards and summary reports.

Percentage of Sales employees:
=COUNTIF(B2:B500,"Sales")/COUNTA(B2:B500)

-- Format the cell as Percentage (%) for a clean display
-- Result: 0.24 becomes 24% - meaning 24% of employees are in Sales

COUNTIF to Check for Duplicates

You can flag duplicate entries by using COUNTIF to check if a value appears more than once in a range.

Flag duplicate employee IDs in column A:
=IF(COUNTIF($A$2:$A$500, A2)>1, "DUPLICATE", "Unique")

-- For each row, this counts how many times that row's ID appears
-- If count > 1, it is a duplicate - show the warning
-- Copy this formula down all rows for a full duplicate check

Part 10: 10 Pro Tips for Using COUNTIF Like an Expert

• Always lock your range. Use $B$2:$B$500 instead of B2:B500 when copying formulas. This prevents the range from shifting and producing wrong counts.
• Use full-column references for big datasets. =COUNTIF(B:B, “Sales”) searches the entire column B and never needs updating when new rows are added.
• Store criteria in a separate cell for flexibility. Put your search term in cell E1 and write =COUNTIF(B:B, E1). Change E1 and the count updates instantly – no formula editing needed.
• Use & to combine operators with cell references. =COUNTIF(C:C, “>”&D1) lets you store the threshold in D1 and compare against it. Without the & technique, this is not possible.
• Check for trailing spaces in your data. If COUNTIF returns 0 and you are sure the value exists, the cell might have invisible trailing spaces. Use TRIM to clean your data first.
• Test on a small range first. Before running COUNTIF on 50,000 rows, test it on 20 rows where you already know the expected answer. This confirms your formula is correct before scaling.
• Use COUNTIF to build a live dashboard. Create a summary section in your workbook with labels in one column and COUNTIF formulas in the next. As your data updates, the dashboard refreshes automatically.
• Combine COUNTIF with conditional formatting. Highlight duplicate values by creating a conditional formatting rule using a COUNTIF formula: highlight rows where =COUNTIF($A$2:$A$500,A2)>1 is TRUE.
• For OR logic, add two COUNTIFs together. =COUNTIF(B:B,”Sales”)+COUNTIF(B:B,”Marketing”) counts employees who are in Sales OR Marketing. No overlap – unless you need to subtract doubles with COUNTIFS.
• Remember: wildcards only work with text, not numbers. =COUNTIF(C:C, “*5*”) will NOT work for numbers. Wildcards are text patterns. For partial number matching, use SUMPRODUCT with ISNUMBER and FIND.

Frequently Asked Questions

Summary – The COUNTIF Cheat Sheet

Six months after that painful Friday evening, Amit is the fastest person in his office to answer data questions. When his director sends a message, Amit has the answer ready before most colleagues even open their file. He uses COUNTIF for single counts, COUNTIFS for multi-condition counts, and stores his criteria in dropdown cells so his dashboard updates live.

He never counts manually. He told a new joiner last week: once you learn COUNTIF, you will feel like you got a superpower. He is right.

Task	Best Formula to Use	Quick Example
Count one type of text	COUNTIF	=COUNTIF(B:B, “Sales”)
Count above or below a number	COUNTIF with > or <	=COUNTIF(C:C, “>10000”)
Count blank cells	COUNTBLANK or COUNTIF	=COUNTBLANK(B:B)  or  =COUNTIF(B:B, “”)
Count non-blank cells	COUNTA or COUNTIF	=COUNTA(B:B)  or  =COUNTIF(B:B, “<>”)
Count partial text matches	COUNTIF with * wildcard	=COUNTIF(A:A, “*Kumar*”)
Count with 2+ conditions (AND)	COUNTIFS	=COUNTIFS(B:B,”Sales”,C:C,”North”)
Count with OR logic	Two COUNTIFs added	=COUNTIF(B:B,”Sales”)+COUNTIF(B:B,”HR”)
Count between two numbers	COUNTIFS on same column	=COUNTIFS(C:C,”>=5000″,C:C,”<=20000″)
Count dates before today	COUNTIF with TODAY()	=COUNTIF(D:D, “<“&TODAY())
Check for duplicates	COUNTIF inside IF	=IF(COUNTIF($A$2:$A$500,A2)>1,”Dup”,”OK”)

The COUNTIF formula is not just a counting tool – it is a thinking tool. It teaches your spreadsheet to ask questions about your data and answer them instantly. Once you are comfortable with COUNTIF, the door opens to SUMIF, AVERAGEIF, and the entire world of condition-based Excel formulas that make professional data work genuinely fast and genuinely accurate.

Free Excel Tools at ibusinessmotivation.com If your data tasks also include merging multiple Excel files, cleaning duplicates, or splitting sheets by department or region – all common tasks before running COUNTIF on clean data – visit ibusinessmotivation.com for free browser-based tools. No VBA, no installation, and no technical knowledge needed.