Whether you're researching keywords, examining lead sources, or analyzing customer attributes from a CRM export, Google Sheets COUNTIF and COUNTIFS formulas can help.
These two spreadsheet formulas only count a cell (from a range of cells) if specific criteria are met.
Recently, a multi-channel reseller wanted to analyze five years worth of lead and conversion data from the customer relationship management software. Leads can come from the reseller's website, its various digital marketing efforts, offline advertising, or people just entering a store.
… a multi-channel reseller wanted to analyze five years' worth of lead and conversion data …
The company sells relatively expensive products ranging from $ 10,000 to $ 50,000 each. Thus, it may take some contacts to close a sale. Understanding which sources produce leads can help the retailer understand how to get more customers.
Unfortunately, the company's CRM did not provide the required report. So a couple of people from the dealer's marketing department exported the data in comma-separated values. This CSV file was uploaded to a Google Sheet, and thanks to COUNTIF and COUNTIFS, it was fairly easy to identify which lead sources generated the most sales.
To show how to use COUNTIF and COUNTIFS formulas, I use sample data.
The data has four columns: an order number, the US state from which the order was placed, the main source and the sales amount. For the examples, I will 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 in the cell range.
I will create a second sheet to analyze lead sources according to US state. This sheet will have a column to list the states, the total number for each state, and the number and percentage of the total for each lead source.
I will use COUNTIF formula to get the total number of orders coming from each state. The formula accepts two parameters, the interval and the criterion.
The area is any set of cells in the current sheet or on another sheet. Our range comes from the "Lead Data" sheet and not the current one.
Typing “= COUNTIF” in the Google Sheets formula field will automatically generate formula options from a list. Select “= COUNTIF” and navigate to the range, then drag to select it.
When a cell contains text, the criterion is cited. For our example, I first wanted to get an account of all sales to California – designated "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 the range uses "Lead Data"! B2: B25 as the range and status ("CA") of the criterion. If the interval had been in the same or current sheet it would not have included the sheet name.
I can use the same strategy to get the bill for each state on the list.
The related COUNTIFS-formula accepts a series of intervals and criteria. We can use it to find the number of leads that are converted from each source, such as Facebook.
The formula is similar to COUNTIF. In this case, we only count rows that have "CA" in the state column.
=COUNTIFS('Lead Data' !B2:B25, "CA")
To this we add a comma, followed by a second interval 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 potential customers from Facebook who converted consumers from each state. For our example, I also wanted to know the percentage of total potential customers that these represented. So I can add a slash and a reference to the total joints 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 in quotation marks that surround the criterion value.
Here are some examples.
- "<> CA" – Not as "CA" where "<>" does not mean equal.
- “> 10” – More than 10.
- “<10” – Less than 10.
- “> = 10” – Greater than or equal to 10.
- “<= 10” – Less than or equal to 10.
wildcard ~~ POS = TRUNC
Finally, there are also two wildcards available for criterion values.
- ? – Matches every character.
- * – Matches zero or more consecutive characters.
To match a real one? or *, place a tilde (~) in front of it. For example, "~?" Match a question mark.