Learn how to use COUNTIF (greater than, less than, not blank…) in Excel and save time calculating data. We’ll show you an example for various conditions so you can compare our examples with your Excel COUNTIF formula.
Ready to master MS Excel?
What is COUNTIF
In Excel, COUNTIF is a function used to count how many cells are meeting a specific criteria.
The syntax of the COUNTIF function is =COUNTIF(range, criteria) with both arguments being required.
range– the cells you want to count if the criteria is met.
criteria – an element determining which cells are counted when using COUNTIF.
Tips for using COUNTIF
When using COUNTIF in Excel, follow these tips to avoid unwanted results:
- enclose the criteria argument in quotes “” when it contains something else than a cell reference or &;
- use wildcard characters for text elements;
- know that COUNTIF is not case sensitive.
What are wildcard characters in Excel?
Use a question mark ? to match any single character and an asterisk * to match any sequence of characters.
For example, “exce?” will be able to recognise “excel” while “exce*” will count “excel“, “excellent“, etc.
How to use COUNTIF: greater than, less than, etc.
You can use the COUNTIF function in Excel to count cells containing numbers greater than a specific value or to count the number of cells that are not blank. This is determined in the criteria argument.
COUNTIF: greater than
Use COUNTIF with “greater than” easily with the formula =COUNTIF(range,”>number“) such as =COUNTIF(B2:B7,”>3″) – the system will count how many cells contain a number greater than 3 from cell B2 to cell B7.
For COUNTIF greater than a cell reference, the formula is =COUNTIF(range,”>”&cell reference)
In this example, we want to calculate how many customers ordered more than 15 times with us during the year.
The COUNTIF formula we use is =COUNTIF(F22:F37,”>15″); the result is 7.
COUNTIF in Excel: less than
Use COUNTIF with “less than” easily with the formulas =COUNTIF(range,”<number“) or =COUNTIF(range,”<“&cell reference) such as =COUNTIF(B2:B7,”<“&C4) – the system will count how many cells contain a number smaller than the one contained in C4, in cells B2 to B7.
In this example, we want to count how many clients ordered less than 5 times during the year.
The COUNTIF formula we use is =COUNTIF(F22:F37,”<5″); the result is 3.
COUNTIF: not blank
Calculate how many cells are not blank with the COUNTIF formula =COUNTIF(range,”*”) such as =COUNTIF(B2:B7,”*”) – the system will count how many cells are not empty, from cells B2 to B7.
Here, we want to double-check we did not forget entering some data in our Excel table.
The COUNTIF formula we use for “not blank” is =COUNTIF(F22:F37,”*”); no cell is blank.
COUNTIF function: equal to
Count how many cells are equal to a specific value (number or text) with the COUNTIF formula =COUNTIF(range,”number/text“); use this other formula when a cell reference is the criteria =COUNTIF(range,cell reference).
In this example, we want to count how many of our customers have their name starting with the letter “t“. We will use the wildcard character *.
The COUNTIF formula we use is =COUNTIF(A22:A37,”t*”); the result is 3. Remember, COUNTIF is not case sensitive.
COUNTIF: greater than or equal to/less than or equal to
Calculate how many cells contain numbers greater than or equal to a specific value using =COUNTIF(range,”>=number“) or =COUNTIF(range,”>=”&cell reference) for the first situation and =COUNTIF(range,”<=number“) or =COUNTIF(range,”<=”&cell reference) for the latter.
In this example, we want to know how many of our customers ordered 5 or more times with us.
The COUNTIF formula we use is =COUNTIF(F22:F37,”>=5″); the result is 13.
In this other example, we aim to count how many yearly orders were greater than or equal to the number in cell B39.
The COUNTIF formula is =COUNTIF(F22:F37,”>=”&B39); the result is 10.
Now, you know how to use COUNTIF: greater than, less than, not blank… rules don’t have any secrets anymore!
Bookmark our website or join our Excel Online Course: we will explain how to use COUNTIFS – COUNTIF with multiple criteria.
Register for the MS Excel Course now and start learning when you are ready!
