Hi Reader,

Welcome to the Google Sheets Tips newsletter #293, your Monday morning espresso, in spreadsheet form!

This week, I'm discussing spreadsheets live on YouTube on the Totally Unscripted show. The episode airs this Wednesday (6th March) at 3 pm Eastern time.

​Tune in here and ask your questions!​

A big thank you to this week's sponsors who help keep this newsletter free to read:

➜ News

I.
​Catch me live this Wednesday at 3 pm Eastern Time, talking about Google Sheets on the Totally Unscripted show.

​Watch the show here​

II.
​You may have noticed the Apps Script outage last Monday. For a few hours, Apps Script was down and we were getting error messages.

​Here's the incident report​

_______

➜ Google Sheets Tip #293: Conditional counts and sums (when certain things are true)

In this tip, we look at the COUNTIF, COUNTIFS, SUMIF, and SUMIFS functions.

Suppose we have this dataset:

Named Ranges

Let's add Named Ranges to make our lives easier. Named ranges are labels that we attach to ranges so that we can refer to them easily in formulas.

Highlight A2:A11 and go to the menu: Data > Named ranges

Name this range "customers".

Now, we can write "customers" instead of "A2:A11" in our formulas.

Repeat for the products and revenue in columns B and C.

Our named ranges should look like this:

Conditional Counts and Sums

Let's run through a series of examples, from simple to more complex.

To count all the "Office Pro" products, we use this COUNTIF formula:

To sum the revenue for the "Office Pro" products, use this SUMIF with the revenue range:

If we use more than one conditional test, we need to use the COUNTIFS or SUMIFS functions.

Here, we use a COUNTIFS to count "Office Pro" products with revenue over $20,000:

And if we want to sum the revenue associated with these products, we use a SUMIFS formula:

Wildcards

These conditional formulas have a hidden power... they accept wildcard characters!

They support three wildcards:

1. The star * matches zero or more characters.
2. The question mark ? matches exactly one character.
3. The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards.

Thus, to count all the "Pro" products (i.e. "Office Pro" and "Alpha Pro Tool") in our data, we use a COUNTIF with star wildcards on either side of "Pro":

This matches any strings containing "Pro", even just "Pro" on its own.

To sum, use a SUMIF with wildcards:

The ? represents a single character.

In our example, we have "Customer 01" and "Customer 03". Suppose we want to count both of these customers, then we can use the ? wildcard to represent the 1 or 3 as follow:

Finally, we can sum with this wildcard character like so:

Note: COUNTIF​, ​COUNTIFS​, ​SUMIF​, and ​SUMIFS​ functions are case insensitive.

E.g. "Office Pro", "OFFICE PRO", or "office pro" will all produce identical results.

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. When you hard-code a number in your spreadsheets​