Quick Links
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, theCOUNT function on Excelestablishes 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. AnEarnedcolumn 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 was0, 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:
If I want to know how many paychecks were released during that week, I use the following formula:
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:
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:
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 thedifferences between COUNTIF and COUNTIFS, the gist is that COUNTIFS allows you to use multiple conditions instead of just one.
To use COUNTIFS:
Here’s one example of what the formula might look like:
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:
Of course, the possibilities of the COUNTIF and COUNTIFS functions are virtually endless. you’re able to take these functions to the next level by combining them withExcel’s wildcard symbolsandnested IF statements in Excel. Ultimately, the right function for you depends on your dataset and what information you need to analyze.
