Often, stockholders request to reformat and summarize data to process and analyze instead of large data build. For example in sales manager need to get items based product list to summarize selling units, number of customers, most selling products, and more. You have to use the count base conditions to return the count across a range. COUNTIF in google sheets helps to return a count of how many times a certain value appears in the datasheet.
This is the basic step to creating COUNTIF in Google Sheets.
- Open a spreadsheet in Google Sheets.
- Identify the data range that you need to test against the criterion
- Select the cell in which you want to return an argument return value.
- Then Type =COUNTIF(range, criterion)
This article describes features of COUNTIF in google sheets and usage with different user requirements with examples and how to implement different condition operations.
Contents
COUNTIF in google sheets obtain the following syntax
Range – The range of cells that users need to be tested against the criterion.
Criterion – operation of pattern which you need to return value of the selected range. As an operation, you can use text or numbers, or special characters. That criterion must be a string & you can use any of the following conditional operators: =, >, >=, <, or <=, which check whether the range cell is equal to, greater than, greater than or equal to, less than, or less than or equal to the criterion value in countif in google sheets.
About exampleOne of our clients sends a sample data sheet of his sportswear sales inventory. He accepts orders online and offline and all sales are processed by sales executives. that client wanted to analyze the past 4-year sales forecast and create a report to get an idea about customer behavior and sales executive performance.
Download Sample Sheet
To Import the example datasheet into your spreadsheet,
- Open a spreadsheet in Google Sheets.
- On the top of navigation click File > Import (import datasheet)
Note : All examples will base on this sample data sheet.
How to use google sheets countif contains text?
There are two types of scenarios when using text criterion with COUNTIF in google sheets.
- Text string must be wrapped in double quotation symbols, such as “Adidas”
- When Text strings can contain “?” and “*” wildcard characters, To match one character “?” wildcard is used, and to match multiple connecting characters “*” wildcard is used, such as “Ad?s” or “*das”
Our client needs to count online sales on the above sportswear sales inventory. Also, need to get the number of sales counts sold by the sales rep whose exit the last name is Lee.
- Open a spreadsheet in Google Sheets.
- Select the Appropriate cell you want to appear countif Result in sheets
Get the number count of ” online ” sales sportswear sales inventory
- in the example sheet, select the K2 column to return the Number of sales online
- Type =COUNTIF($G2:$G, “online”). G2:G column range represents all sell types of sportswear sales inventory, criterion represent return value of the selected range. Make sure that need to wrap within double quotation
Get the number of sales counts sold by the sales rep whose exit the last name is “Lee“
- in the example sheet, select the J3 column to return the sales rep whose exit the last name is Lee
- Type =COUNTIF($B2:$B, “*Lee”) B2:B column range represents all sales rep name in sportswear sales inventory, criterion represent return value of the selected range. Add “*” wildcard characters first and Lee as second
How to use google sheets countif contains numbers?
Our client needs to find and count the number of sales whose bills total more than 700 USD. it needs to check the count of the Total (F) column in the sportswear sales inventory
- in the example sheet, select the J4 column to return the number of sales whose bills total more than 700 USD
- Type =COUNTIF($F3:$F,”>700″) F2:F column range represents sales total in sportswear sales inventory, criterion represent with greater than conditional operation symbol with 700
You can use the following operation with countif in google sheets
- =COUNTIF(F2:F, “<>700”) — Not equal “700” where “<>” means not equal
- =COUNTIF(F2:F, “>700”) — Greater than 700
- =COUNTIF(F2:F, “<700”) — Less than 700.
- =COUNTIF(F2:F, “>=700”) — Greater than or equal to 700
- =COUNTIF(F2:F, “<=700”) — Less than or equal to 700.
Make sure that the criterion value needs to be inside the double quotation marks wrapping the criterion value.
How to use countif in google sheets with a date condition?
You can also include the date as a criterion value in the test condition, but as we mention on the beginning of this article criterion value need to be represented as a string and wrapped inside double quotation marks. But when using date and criterion values, string wrapping cannot use to return date-related conditions. We have to combine the DATE function on the side of countif function as a criterion we can combine the operation symbol and wildcard characters with a double-quote and the ampersand (&) mark.
Our client needs to find and count the number of sales after the bill date of 01/01/2020.
- In the example sheet, select the J5 column to return the number of sales after the bill date of 01/01/2020.
- Type =COUNTIF($A4:$A,”>=”&DATE(2020,1,1)) $A2:$A represent date of each sales of the sportswear sales inventory, in criterion section exit >= inside double quote and DATE(2020,1,1) combine with the ampersand(&) mark.
Conclusion
Google sheets are now everywhere, and most sheet user are recommended to google sheets in one top-level cloud tool that resolves all sheet matters.countif is google sheets most useful function that use to return summarise in date set.
