Are you searching for a way to analyze data in Google Sheets? Look no further! With the COUNTIF and COUNTIFS formulas, you can easily count cells in a range that meet specific criteria. Whether you’re conducting keyword research, examining lead sources, or analyzing customer attributes from a CRM export, these powerful spreadsheet formulas can help you make sense of your data.
Imagine this scenario: a multi-channel retailer wants to analyze five years’ worth of lead and conversion data from their CRM software. Leads can come from various sources, such as the retailer’s website, digital marketing efforts, offline advertising, or in-store visits. As the retailer sells relatively expensive products ranging from $10,000 to $50,000 each, it often takes multiple contacts to close a sale. By identifying the lead sources that generate the most sales, the retailer can target new customers effectively.
Unfortunately, the company’s CRM software didn’t provide the required report. However, the clever marketers at the retailer exported the data as a CSV file and imported it into Google Sheets. Using the COUNTIF and COUNTIFS formulas, they were able to quickly identify the lead sources that generated the highest number of sales.
In this article, we’ll demonstrate how to apply COUNTIF and COUNTIFS using example data.
COUNTIF
Let’s consider a dataset with four columns: order number, US state, lead source, and sales amount. For this example, we’ll focus only on the state and lead source columns. The sheet name for this data is “Lead Data”. To analyze lead sources by US state, we’ll create a separate sheet that lists the states, the total count for each state, and the count and percentage for each lead source.
To get the total count of orders from each state, we’ll use the COUNTIF formula, which accepts two parameters: the range and the criteria.
The range can be a set of cells within the current sheet or another sheet. In our case, the range will come from the “Lead Data” sheet instead of the current sheet.
Here’s an example formula for counting all sales from California (designated by “CA” in the state column):
=COUNTIF('Lead Data'!B2:B25, "CA")
In this formula, we specify the sheet and range as ‘Lead Data’!B2:B25 and the criteria as “CA”. If the range had been in the same sheet or the current sheet, we wouldn’t need to include the sheet name.
We can use this same approach to get the count for each state in the list.
COUNTIFS
The COUNTIFS formula takes multiple ranges and criteria pairs. We can use it to find the number of leads converted from each source, such as Facebook.
The formula is similar to COUNTIF. In this case, we’re only counting rows that contain “CA” in the state column.
=COUNTIFS('Lead Data'!B2:B25, "CA")
To this, we add a comma, followed by a second range and criterion – in this example, Facebook.
=COUNTIFS('Lead Data'!B2:B25, "CA", 'Lead Data'!C2:C25, "Facebook")
By modifying the state criterion, we can find the number of leads from Facebook that converted consumers from each state. For our example, we also want to know the total percentage of leads represented by these conversions. So, we can add a forward slash and a reference to the total leads for a given state.
=COUNTIFS('Lead Data'!B2:B25, "CA", 'Lead Data'!C2:C25, "Facebook")/B3
Operators
You can also include operators in the criterion value for COUNTIF or COUNTIFS. Simply place them between the quotation marks surrounding the criterion value.
Here are some examples:
<>CA– Not equal to “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.
Wildcards
Lastly, there are two wildcard characters available for criterion values.
?– Matches any single character.*– Matches zero or more contiguous characters.
To match a ? or * as a literal character, precede it with a tilde (~). For example, ~? would match a question mark.
Now that you know how to use COUNTIF and COUNTIFS in Google Sheets, you can easily sort and analyze your data to make informed decisions. For more advanced techniques and tips for Google Sheets, visit Crawlan.com and become a spreadsheet expert!
