Whether or not you’re researching key phrases, reviewing lead sources, or analyzing buyer attributes from a CRM export, Google Sheets’ COUNTIF and COUNTIFS formulation may also help.

These two spreadsheet formulation will solely depend a cell (from a spread of cells) if particular standards are met.

Just lately, a multichannel retailer needed to investigate 5 years’ price of lead and conversion information from its buyer relationship administration software program. Leads would possibly come from the retailer’s web site, its numerous digital advertising and marketing efforts, offline promoting, or of us simply strolling right into a retailer.

…a multichannel retailer needed to investigate 5 years’ price of lead and conversion information…

The corporate sells comparatively costly merchandise that vary from $10,000 to $50,000 every. Thus, it could take a couple of contacts to shut a sale. Understanding which sources produce leads might assist the retailer perceive find out how to get extra prospects.

Sadly, the corporate’s CRM didn’t present the required report. So a few of us from the retailer’s advertising and marketing division exported the info in comma-separated values format. This CSV file was uploaded to a Google Sheet, and due to COUNTIF and COUNTIFS, it was pretty straightforward to establish which lead sources produced essentially the most gross sales.

To exhibit find out how to apply COUNTIF and COUNTIFS formulation, I’ll use pattern information.

COUNTIF

The information has 4 columns: an order quantity, the U.S. state from which the order was positioned, the lead supply, and the sale quantity. For the examples, I’ll concentrate on simply two of those columns: the state and the lead supply. The identify of the sheet is “Lead Information.” Discover that I included this identify the vary of cells.

The instance or pattern information because it appeared in a Google Sheet. Discover the 4 columns of knowledge; our examples will concentrate on state and lead supply. Click on picture to enlarge.

I’ll create a second sheet to investigate lead sources by U.S. state. This sheet can have a column to checklist the states, the full depend for every state, and the depend and proportion of the full for every lead supply.

A second sheet will make use of the COUNTIF and COUNTIFS formulation. It features a pair of columns — “Depend” and “%” —  for every lead supply. Every state’s depend is in a row. Click on picture to enlarge.

I’ll use the COUNTIF method to get the full variety of orders originating from every state. The method accepts two parameters, the vary and the criterion.

=COUNTIF(vary, criterion)

The vary is any set of cells within the present sheet or one other sheet. Our vary will come from the “Lead Information” sheet and never the present one.

Typing  “=COUNTIF” into the method bar in Google Sheets will auto-generate method choices from a listing. Choose “=COUNTIF” and navigate to the vary after which drag to pick it.

Google Sheets will acknowledge the COUNTIF method as you begin to kind it. Click on picture to enlarge.

When a cell comprises textual content, the criterion is quoted. For our instance, I first needed to get a depend of all the gross sales to California — designated as “CA” within the cells of the state column. That is what the COUNTIF method appeared like:

=COUNTIF('Lead Information' !B2:B25, "CA")

The vary ought to seem as you choose it within the Google Sheets’ method bar. Then kind a comma and the criterion worth. Click on picture to enlarge.

The part describing the sheet and vary makes use of ‘Lead Information’ !B2:B25 because the vary and the state (“CA”) for the criterion. If the vary had been in the identical or present sheet, it might not have included the sheet identify.

I can use this identical method to get the depend for every state on the checklist.

Use the COUNTIF method to acquire a depend for every of the states in consideration. Click on picture to enlarge.

COUNTIFS

The associated COUNTIFS method will settle for a collection of ranges and criterion pairs. We will use it to search out the variety of leads that transformed from every supply, comparable to Fb.

The method is much like COUNTIF. On this case, we’re counting solely rows which have “CA” within the state column.

=COUNTIFS('Lead Information' !B2:B25, "CA")

To this, we add a comma, adopted by a second vary and a second criterion — Fb on this instance.

=COUNTIFS('Lead Information' !B2:B25, "CA", 'Lead Information' !C2:C25, "Fb")

The COUNTIFS method will enable for a number of collection of vary and criterion pairs separated by a comma. Click on picture to enlarge.

Altering the criterion for the state will give us a depend of the leads from Fb that transformed shoppers from every state. For our instance, I additionally needed to know the share of complete leads these represented. So I can add a slash and a reference to the full leads for a given state.

=COUNTIFS(('Lead Information' !B2:B25, "CA", 'Lead Information'!C2:C25, "Fb")/B3)

Use the COUNTIFS method for every state and every lead supply. The ensuing depend could be divided by the full variety of transformed leads for the state to get a proportion. Click on picture to enlarge.

Operators

You may as well embrace operators within the criterion worth for both COUNTIF or COUNTIFS. Place these contained in the citation marks surrounding the criterion worth.

Listed below are some examples.

  • “CA” — Not equal “CA” the place “” means not equal.
  • “>10” — Larger than 10.
  • “<10” — Lower than 10.
  • “>=10” — Larger than or equal to 10.
  • “<=10” — Lower than or equal to 10.

Wildcards

Lastly, there are additionally two accessible wildcard characters for criterion values.

  • ? — Matches any single character.
  • * — Matches zero or extra contiguous characters.

To match an precise ? or *, put a tilde (~) in entrance of it. For instance, “~?” would match a query mark.