COUNTIF formula and example in Excel and Google Sheets

Formula, examples and how to use it

=COUNTIF(range, criterion)

One of the most popular functions used in Excel and Google Sheets, and COUNT values based on a condition. In this exercise, we will analyze the "Oil and gas" industry

COUNTIF function in Excel and Google Sheets

COUNTIF FORMULA AND EXAMPLE AND RESULT — EXCEL & GOOGLE SHEETS EXAMPLE

In the above datasheet, let's find out the total revenue for the "Oil and gas" industry. Since our data set is small we can simply count. If we have 1000s row of the data we can use the COUNTIF function to get the result and the formula will look like this.

💡 "Oil and gas" = criteria are not case sensitive "Oil and Gas", "OIL AND GAS", "Oil And Gas" will produce the same result. As long we have the same text within the criteria, we will get the same result

💡 "1 Oil and Gas", "1 OIL AND GAS", "1 Oil And Gas"  will NOT produce the same result.

💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel

💡 Below explained how to count if a text contains part of the string

How to COUNTIF cells if contains part of a string 

HOW TO COUNTIF CELLS IF CONTAINS PART OF A TEXT STRING — EXCEL AND GOOGLE SHEETS EXAMPLE

💡 Wildcard: The * character allows for any number (including zero) of other characters to take its place.

💡 In this example, it’s used to find all cells that include the text "Oil and gas". This search is not case-sensitive, so "Oil and gas" are considered the same as "OIL AND GAS" or "Oil And Gas".

💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel

💡 Below explained how to count if cells start with a certain value

How to COUNTIF cells start with a certain value

HOW TO COUNTIF CELLS START WITH A CERTAIN VALUE — EXCEL AND GOOGLE SHEETS EXAMPLE

💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel

💡 Below explained how to count if cells end with a certain value

How to COUNTIF cells end with a certain value

HOW TO COUNTIF CELLS END WITH A CERTAIN VALUE — EXCEL AND GOOGLE SHEETS EXAMPLE

💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel