This syntax takes a little getting used to.
See below for many working examples.
When cells meet the criteria, they are added to the count.
In the worksheet below, we have a small amount of sales data.
The tricky part about using the COUNTIF function is the syntax needed to apply criteria.
This is because COUNTIF is in a group ofeight functionsthat split logical criteria into two parts:rangeandcriteria.
Because of this design, operators must be enclosed in double quotes ("").
This makes it easy to change criteria later without editing the original formula.
Notice the greater than operator (>), which is text, must be enclosed in quotes.
The most reliable method is to refer to a date in another cell or use theDATE function.
to match a question mark (?)
that occurs at the end of “Michigan?”
and “Montana?”.
The tilde (~) is an escape character that allows you to find aliteralwildcard.
To match a question mark (?
), use “~?
Summary Table
you’re able to use COUNTIF to create a simple summary table of counts.
In the worksheet below, we have a list of unique colors in F5:F9.
The goal is to generate a count for each color.
For more advanced users, this is an important limitation in how COUNTIF works and what it can do.
One of the more tricky limitations of COUNTIF is that it won’t allow anarrayfor therangeargument.
This would be cool if it worked.
However, Excel won’t even let you enter this formula.
If you try, you’ll get a generic “There’s a problem with your formula” error.
However, SUMIF is not programmed to handle arrays, so it won’t work.
How can we work around this problem?
However, the latest version of Excel offers powerful alternatives, includingBYROW,BYCOL, andGROUPBY.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
COUNTA does not count empty cells.
