Whether you’re researching keywords, reviewing lead sources, or analyzing customer attributes from a CRM export, Google Sheets’ COUNTIF and COUNTIFS formulas can help.
These two spreadsheet formulas will only count a cell (from a range of cells) if specific criteria are met.
Recently, a multichannel retailer wanted to analyze five years’ worth of lead and conversion data from its customer relationship management software. Leads might come from the retailer’s website, its various digital marketing efforts, offline advertising, or folks just walking into a store.
…a multichannel retailer wanted to analyze five years’ worth of lead and conversion data…
The company sells relatively expensive products that range from $10,000 to $50,000 each. Thus, it may take a few contacts to close a sale. Understanding which sources produce leads could help the retailer understand how to get more customers.
Unfortunately, the company’s CRM did not provide the required report. So a couple of folks from the retailer’s marketing department exported the data in comma-separated values format. This CSV file was uploaded to a Google Sheet, and thanks to COUNTIF and COUNTIFS, it was fairly easy to identify which lead sources produced the most sales.
To demonstrate how to apply COUNTIF and COUNTIFS formulas, I’ll use sample data.
The data has four columns: an order number, the U.S. state from which the order was placed, the lead source, and the sale amount. For the examples, I’ll focus on just two of these columns: the state and the lead source. The name of the sheet is “Lead Data.” Notice that I included this name the range of cells.
I will create a second sheet to analyze lead sources by U.S. state. This sheet will have a column to list the states, the total count for each state, and the count and percentage of the total for each lead source.
I will use the COUNTIF formula to get the total number of orders originating from each state. The formula accepts two parameters, the range and the criterion.
The range is any set of cells in the current sheet or another sheet. Our range will come from the “Lead Data” sheet and not the current one.
Typing “=COUNTIF” into the formula bar in Google Sheets will auto-generate formula options from a list. Select “=COUNTIF” and navigate to the range and then drag to select it.
When a cell contains text, the criterion is quoted. For our example, I first wanted to get a count of all of the sales to California — designated as “CA” in the cells of the state column. This is what the COUNTIF formula looked like:
=COUNTIF('Lead Data' !B2:B25, "CA")
The section describing the sheet and range uses ‘Lead Data’ !B2:B25 as the range and the state (“CA”) for the criterion. If the range had been in the same or current sheet, it would not have included the sheet name.
I can use this same approach to get the count for each state on the list.
The formula is similar to COUNTIF. In this case, we are counting only rows that have “CA” in the state column.
=COUNTIFS('Lead Data' !B2:B25, "CA")
To this, we add a comma, followed by a second range and a second criterion — Facebook in this example.
=COUNTIFS('Lead Data' !B2:B25, "CA", 'Lead Data' !C2:C25, "Facebook")
Changing the criterion for the state will give us a count of the leads from Facebook that converted consumers from each state. For our example, I also wanted to know the percentage of total leads these represented. So I can add a slash and a reference to the total leads for a given state.
=COUNTIFS(('Lead Data' !B2:B25, "CA", 'Lead Data'!C2:C25, "Facebook")/B3)
You can also include operators in the criterion value for either COUNTIF or COUNTIFS. Place these inside the quotation marks surrounding the criterion value.
Here are some examples.
- “<>CA” — Not equal “CA” where “<>” means not equal.
- “>10” — Greater than 10.
- “<10” — Less than 10.
- “>=10” — Greater than or equal to 10.
- “<=10” — Less than or equal to 10.
Finally, there are also two available wildcard characters for criterion values.
- ? — Matches any single character.
- * — Matches zero or more contiguous characters.
To match an actual ? or *, put a tilde (~) in front of it. For example, “~?” would match a question mark.