Count Cells With Specific Text in Excel Using the COUNTIF Formula

Key Takeaways

• Excel’s COUNT function produces a number of cells containing any value, but only COUNTIF and COUNTIFS can use conditions to narrow your results.
• The COUNTIF function is used for one condition, while COUNTIFS allows multiple conditions to be specified in Excel.

When you’re staring at a massive spreadsheet, counting cells manually isn’t just tedious—it’s a recipe for mistakes. Thankfully, Microsoft Excel’s COUNTIF formula swoops in to save the day. It lets you count cells based on specific text or conditions, turning your spreadsheet chaos into organized data magic.

How to Count Excel Cells Based on a Condition

Not to be confused with the SUM function, which adds up numerical values within a group of cells, the COUNT function on Excel establishes the number of cells that contain a value.

In the image above, the COUNT function produces the number of paychecks released in a single week. But in its basic form, this function will only take you so far. Its sister functions, COUNTIF and COUNTIFS, can narrow those results into only the cells that meet a condition or have a specific string.

For example, you might need to calculate only the number of checks over a certain dollar amount or paychecks related to particular people. In these cases, you’ll need to use COUNTIF and COUNTIFS.

Excel’s COUNTIF and COUNTIFS functions are exactly how they sound: they will count cells for you IF those cells meet criteria that you specify.

How to Count Cells With COUNTIF

You should use COUNTIF when you have just one condition to factor in. This function is handy when you need to count cells that exceed (or fall under) a specified number.

In the above image, an Excel sheet breaks down the payroll for two separate pay weeks. An Earned column contains a formula that calculates the amount of money a performer earned depending on a session fee and number of sessions. On a week during which a performer had no sessions, their total earned was 0, meaning they did not receive a paycheck.

Whereas the basic COUNT function would consider a 0 to be a numerical value and hence count it as a paycheck, I can instead use COUNTIF to clarify that I only want to count cells with numerical values over 0.

To use COUNTIF:

1. Select the cell in which you’d like to display your COUNTIF output.
2. Type in the =COUNTIF function.
3. Within a set of parentheses, you should first include a cell range of where you want the function to look, a comma, and then what (a value, for instance) you want the function to look for.
4. Hit Enter.

If I want to know how many paychecks were released during that week, I use the following formula:

=COUNTIF(E3:E8, >"0") 

The COUNTIF function is also useful when you have a dataset that contains repetitive values. Say I was looking at a statement of paychecks over a wide date range, and I needed to quickly count checks per person.

I could establish a column with each performer’s name, and use that cell number as the condition to count their paychecks:

=COUNTIF(A3:A9, F3) 

If I didn’t want to include a name column, I could alternatively use the exact text of their names in the COUNTIF function instead:

=COUNTIF(A3:A9, "P. King") 

Both of these functions will output the same result.

How to Count Cells With COUNTIFS

Maybe you’re set at this stage, or perhaps you need to implement additional conditions. Luckily for you, COUNTIF walked so COUNTIFS could run. Regarding the differences between COUNTIF and COUNTIFS, the gist is that COUNTIFS allows you to use multiple conditions instead of just one.

To use COUNTIFS:

1. Select the cell in which you’d like to display your COUNTIFS output.
2. Type in the =COUNTIFS function.
3. A set of parentheses includes a cell range, a comma, the first value or condition you’d like the program to look for, a comma, a second cell range, a comma, and another condition.
4. Hit Enter.

Here’s one example of what the formula might look like:

=COUNTIFS(B3:B18, G3, E3:E18, ">0") 

In this example, the spreadsheet calculates the number of paychecks per performer over multiple weeks by first specifying the person and secondarily establishing that we’re looking for any value over 0.

You might also want to not only separate paychecks per performer but also separate lower paycheck values from higher paycheck values.

In this example, which is based on a list of payroll checks released over a range of dates, the following function will establish the amount of high-value paychecks that were over $1,000 for a specific performer:

=COUNTIFS(A3:A9, "P. King", B3:B9, ">1000") 

Of course, the possibilities of the COUNTIF and COUNTIFS functions are virtually endless. You can take these functions to the next level by combining them with Excel’s wildcard symbols and nested IF statements in Excel. Ultimately, the right function for you depends on your dataset and what information you need to analyze.