How to use COUNTIF: greater than

COUNTIF: greater than, less than, not blank; this Excel function is so handy! Learn how to use it in your spreadsheets with Ted Jordan.

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?

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.

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.

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.

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.

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.

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.

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.

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!

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More
Programmatic: Pixels in Advertising 101

Advertising pixels: types, pros and best practices

Learn the basics of Programmatic Advertising with Ted Jordan! Today, we explain what a pixel is in advertising with simple words and expertise. Ready to learn?

Read More

Categories

AllFunctionsToolsShortcut Keys

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More
Split Semicolon Separated Value in Excel

How to split semicolon separated values in Excel

Split semicolon separated values in Excel into columns and rows, or convert columns to semicolon separated values, like an Excel Master!

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More

How to use the IFERROR function (Excel + Google Sheets)

Discover how to use IFERROR in Excel or in Google Sheets in 4 easy steps! No more errors on your documents or dashboards with IFERROR.

Read More