✦Template Printable Ideas Insights

Mastering COUNTIF for Text in Google Sheets: A Comprehensive Guide

By

The COUNIF function in Google Sheets is a powerful tool for data analysis, enabling users to count cells based on criteria. While it's most commonly used to count numbers or dates, did you know you can also use it to count words? This article will guide you through using the COUNIF function in Google Sheets to count words, including how to use wildcards and exact matches.

Before we dive in, ensure your Google Sheets is up to date and familiarize yourself with the COUNIF function syntax: `=COUNTIF(range, criteria)`. The 'range' is the cells you want to evaluate, and 'criteria' is what you're looking for within those cells.

Counting Words in Google Sheets

The COUNIF function doesn't directly support counting words, but we can use additional functions like LEN and SEARCH, or arrays and textiles, to achieve this. Let's explore a simple method using LEN and wildcards.

The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
COUNT functions in google sheet or excel
COUNT functions in google sheet or excel
How to Use COUNTIF on Google Sheets, Infographic
How to Use COUNTIF on Google Sheets, Infographic

Assume you have the following data in Column A: "Apple", "Banana", "Apple", "Cherry", "Apple Banana". To count the occurrences of "Banana", use the following formula: `=COUNTIF(A:A, "*Banana*")`. The asterisks (*) act as wildcards, matching any text before or after "Banana".

Using Wildcards to Count Words

Wildcards are your friends when it comes to counting words. A single asterisk (*) matches any number of characters, while two asterisks (**) match any length of characters or none at all. For example, to count words ending with "le", use: `=COUNTIF(A:A, "le*")`.

How to Check the Word Count in Google Sheets - Make Tech Easier
How to Check the Word Count in Google Sheets - Make Tech Easier
COUNT vs COUNTA vs COUNTBLANK vs COUNTIF - Explained Clearly
COUNT vs COUNTA vs COUNTBLANK vs COUNTIF - Explained Clearly
How to See Word Count in Google Docs As You’re Writing
How to See Word Count in Google Docs As You’re Writing

Let's say you want to count words containing "an". The formula `=COUNTIF(A:A, "*an*")` will count "banana", "Apple banana", but not "Canary" or "Pineapple". The asterisks ensure that "an" can appear anywhere within the word.

Counting Words using Exact Matches

While wildcards are versatile, sometimes you need an exact match. In Google Sheets, use the REPLACE function with an IF to achieve this. Here's an example to count occurrences of "Banana" exactly: `=COUNTIF(REPLACE(A:A, SEARCH("Banana", A:A), LEN(A:A), "") & "", "")`.

This formula works in three steps: SEARCH finds the position of "Banana", REPLACE replaces "Banana" with an empty string (""), and the ampersand (&) concatenates an additional empty string, making the text empty whenever "Banana" appears exactly.

Basic Formatting in Google Sheets
Basic Formatting in Google Sheets
Google Sheets Guide: COUNTIFS Function
Google Sheets Guide: COUNTIFS Function
Google Sheets Guide: COUNTIF Function
Google Sheets Guide: COUNTIF Function
the google sheets has hidden gems you're not using on your webpages
the google sheets has hidden gems you're not using on your webpages
Coloring automatically in Google Sheets
Coloring automatically in Google Sheets
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)

Counting Words in Multiple Columns or Ranges

Now, what if your data spans multiple columns or non-continuous ranges? You can use arrays or the UNMAX (UNIQUE + MATCH) technique to count words across various ranges.

Using arrays, you can combine multiple ranges into one. For example, to count "Banana" in columns A, B, and C, use: `=COUNTIF({A:A, B:B, C:C}, "*Banana*")`. The curly braces ({}) combine the ranges into a single entity.

Using UNMAX Technique

The UNUMX technique involves using UNIQUE and MATCH functions to count words across non-continuous ranges. First, create a table of unique values from your ranges. Then, use MATCH to find where these unique values appear in your original data. Finally, use COUNIF to count the occurrences of your target word in these unique values.

Handy Cheat Sheets – Microsoft Office | CustomGuide
Handy Cheat Sheets – Microsoft Office | CustomGuide
How to Calculate Running Total in Google Sheets
How to Calculate Running Total in Google Sheets
the 13 google sheets shortcuts list is shown in green and has white writing on it
the 13 google sheets shortcuts list is shown in green and has white writing on it
How to Convert Text to Number in Excel & Google Sheets
How to Convert Text to Number in Excel & Google Sheets

Here's a detailed breakdown of this method: 1. Use UNIQUE to create an array of unique values from your ranges: `=UNIQUE({A:A, C:C, E:E})`. 2. Use MATCH to find the position of these unique values in your original ranges: `=MATCH(unique_values, A:A), C:C, E:E`. 3. Use COUNIF to count your target word in the unique values: `=COUNTIF(unique_values, "*your_word*")`.

Incorporating the COUNIF function to count words in Google Sheets may seem daunting at first, but with practice, you'll find it intuitive and powerful. Experiment with different formulas and techniques to find what works best for your specific needs. Happy counting!