Working with COUNTIF, COUNTIFS in Google Sheets to Form, Review Knowledge


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.

COUNTIF

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.

The example or sample data as it appeared in a Google Sheet. Notice the four columns of data our examples will focus on state and lead source.

The case in point or sample data as it appeared in a Google Sheet. Detect the four columns of data our examples will emphasis on state and direct supply. Click graphic to enlarge.

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.

A next sheet will make use of the COUNTIF and COUNTIFS formulation. It consists of a pair of columns — “Count” and “Percent” —  for every single direct supply. Each and every state’s rely is in a row. Click graphic to enlarge.

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.

=COUNTIF(assortment, 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.

Google Sheets will recognize the COUNTIF formula as you start to type it.

Google Sheets will recognize the COUNTIF components as you start off to kind it. Click graphic to enlarge.

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 range should appear as you select it in the Google Sheets' formula bar. Then type a comma and the criterion value.

The assortment need to show up as you find it in the Google Sheets’ components bar. Then kind a comma and the criterion value. Click graphic to enlarge.

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.

Use the COUNTIF formula to obtain a count for each of the states in consideration.

Use the COUNTIF components to receive a rely for every single of the states in consideration. Click graphic to enlarge.

COUNTIFS

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")
The COUNTIFS formula will allow for several series of range and criterion pairs separated by a comma.

The COUNTIFS components will permit for a number of sequence of assortment and criterion pairs divided by a comma. Click graphic to enlarge.

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)
Use the COUNTIFS formula for each state and each lead source. The resulting count can be divided by the total number of converted leads for the state to get a percentage.

Use the COUNTIFS components for every single state and every single direct supply. The ensuing rely can be divided by the overall number of converted leads for the state to get a proportion. Click graphic to enlarge.

Operators

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.

Wildcards

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.



Source url

قالب وردپرس

Leave a Reply

Your email address will not be published. Required fields are marked *

Translate »
%d bloggers like this: