Irrespective of whether you’re studying keywords and phrases, examining direct resources, or examining shopper characteristics from a CRM export, Google Sheets’ COUNTIF and COUNTIFS formulation can help.
These two spreadsheet formulation will only rely a mobile (from a assortment of cells) if unique conditions are met.
Not long ago, a multichannel retailer required to review five years’ really worth of direct and conversion data from its shopper marriage administration software. Qualified prospects could possibly come from the retailer’s web-site, its a variety of digital marketing and advertising initiatives, offline promotion, or individuals just strolling into a retailer.
…a multichannel retailer required to review five years’ really worth of direct and conversion data…
The corporation sells somewhat high-priced goods that assortment from $ten,000 to $fifty,000 every single. Therefore, it may possibly acquire a few contacts to near a sale. Knowing which resources make leads could help the retailer understand how to get a lot more customers.
Unfortunately, the company’s CRM did not supply the required report. So a few of individuals from the retailer’s marketing and advertising office exported the data in comma-divided values format. This CSV file was uploaded to a Google Sheet, and many thanks to COUNTIF and COUNTIFS, it was pretty effortless to recognize which direct resources made the most gross sales.
To demonstrate how to apply COUNTIF and COUNTIFS formulation, I’ll use sample data.
The data has four columns: an get number, the U.S. state from which the get was positioned, the direct supply, and the sale quantity. For the examples, I’ll emphasis on just two of these columns: the state and the direct supply. The identify of the sheet is “Lead Knowledge.” Detect that I bundled this identify the assortment of cells.
I will produce a next sheet to review direct resources by U.S. state. This sheet will have a column to checklist the states, the overall rely for every single state, and the rely and proportion of the overall for every single direct supply.
I will use the COUNTIF components to get the overall number of orders originating from every single state. The formula accepts two parameters, the assortment and the criterion.
The assortment is any established of cells in the recent sheet or one more sheet. Our assortment will come from the “Lead Data” sheet and not the recent 1.
Typing “=COUNTIF” into the components bar in Google Sheets will auto-produce components alternatives from a checklist. Decide on “=COUNTIF” and navigate to the assortment and then drag to find it.
When a mobile has text, the criterion is quoted. For our case in point, I first required to get a rely of all of the gross sales to California — designated as “CA” in the cells of the state column. This is what the COUNTIF components seemed like:
=COUNTIF('Lead Data' !B2:B25, "CA")
The part describing the sheet and assortment utilizes ‘Lead Data’ !B2:B25 as the assortment and the state (“CA”) for the criterion. If the assortment experienced been in the similar or recent sheet, it would not have bundled the sheet identify.
I can use this similar method to get the rely for every single state on the checklist.
The associated COUNTIFS components will accept a sequence of ranges and criterion pairs. We can use it to discover the number of leads that converted from every single supply, these kinds of as Facebook.
The components is very similar to COUNTIF. In this circumstance, we are counting only rows that have “CA” in the state column.
=COUNTIFS('Lead Data' !B2:B25, "CA")
To this, we increase a comma, adopted by a next assortment and a next criterion — Facebook in this case in point.
=COUNTIFS('Lead Data' !B2:B25, "CA", 'Lead Data' !C2:C25, "Facebook")
Altering the criterion for the state will give us a rely of the leads from Facebook that converted individuals from every single state. For our case in point, I also required to know the proportion of overall leads these represented. So I can increase a slash and a reference to the overall leads for a supplied state.
=COUNTIFS(('Lead Data' !B2:B25, "CA", 'Lead Data'!C2:C25, "Facebook")/B3)
You can also include things like operators in the criterion value for both COUNTIF or COUNTIFS. Position these within the quotation marks surrounding the criterion value.
Here are some examples.
- “<>CA” — Not equivalent “CA” the place “<>” implies not equivalent.
- “>10” — Bigger than ten.
- “<10” — Less than 10.
- “>=10” — Bigger than or equivalent to ten.
- “<=10” — Less than or equal to 10.
Finally, there are also two available wildcard figures for criterion values.
- ? — Matches any solitary character.
- * — Matches zero or a lot more contiguous figures.
To match an precise ? or *, put a tilde (~) in entrance of it. For case in point, “~?” would match a dilemma mark.