Using COUNTIF, COUNTIFS in Google Sheets to sort, analyze data


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.

COUNTIF

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.

The example or sample data as it appeared in a Google sheet. Notice the four columns of data; our examples will focus on permits and lead sources.

The example or sample data as it appeared in a Google sheet. Notice the four columns of data; our examples will focus on permits and lead sources. Click on the image to enlarge.

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.

In a second sheet, COUNTIF and the COUNTIFS formulas are used. It contains a couple of columns – "Count" and "Percent" – for each lead source. Each state's number is in a row. Click on the image to enlarge.

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.

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

Google Sheets will recognize the COUNTIF formula when you start writing it.

Google Sheets will recognize the COUNTIF formula when you start writing it. Click on the image to enlarge.

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 interval should be displayed when you select it in the Google Sheets format. Then write a comma and the criterion value.

The area should appear when you select it in the Google Sheets formula bar. Then write a comma and the criterion value. Click on the image to enlarge.

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.

Use the COUNTIF formula to get an account for each of the states concerned.

Use the COUNTIF formula to get an account for each of the states concerned. Click on the image to enlarge.

COUNTIFS

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")
The COUNTIFS formula enables several series of interval and criteria separated by commas.

The COUNTIFS formula enables several series of interval and criteria separated by commas. Click on the image to enlarge.

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

Use the COUNTIFS formula for each state and source. The resulting number can be divided by the total number of converted leads to give the state a percentage. Click on the image to enlarge.

operators

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.



Source link

Leave a Comment