Google Sheets Formulas: How to Use SUMIF, COUNTIF, and AVERAGEIF Like a Pro

Are you drowning beneath a sea of spreadsheet data, tired of constantly sorting through endless rows of data manually? I promise there is a better solution!

If you want to effectively summarize your spreadsheet data based on specific criteria, Google’s built-in data sorting filters simply won't cut it.

So, how can you truly analyze and take control of your Google Sheets data with precision?

The answer lies in these three powerful functions: SUMIF, COUNTIF, and AVERAGEIF.

These dynamic Google Sheet functions let you automatically calculate totals, counts, and averages based on specific conditions, all with no tedious sorting or restructuring required.

In this step-by-step guide, you’ll learn exactly how to use these formulas like a pro to simplify your workflows, reduce errors, and unlock actionable insights in your spreadsheets.

So, are you ready to transform the way you work with Google Sheets? Let’s dive in.

Read more: How to use Google Sheets conditional formatting? 

All three functions (SUMIF, COUNTIF, and AVERAGEIF) are Google Sheets conditional ‘IF’ functions with a common purpose.

Each function (formula) applies a specific condition (or criteria) to a range of data and then returns a result based only on the values that meet that condition.

To put it simply, these functions filter your data based on rules you set, then perform these calculations:

• SUMIF: Adds the matching values
• COUNTIF: Counts how many values match.
• AVERAGEIF: Averages the matching values.

If you’re looking to analyze subsets of data, automate reports, or simply want to eliminate having to manually sort or filter information, these functions are incredibly useful!

Let’s take a closer look at each function and its purpose now.

The SUMIF function in Google Sheets allows you to effortlessly add up values that meet a specific condition.

For example, you could add up your total sales from a particular region, add expenses for one department, or calculate total revenue for a single product line.

The COUNTIF function essentially counts how many cells in a given range meet a specified condition.

For small teams, it’s beneficial for tracking how many times a customer has placed an order or how often an employee has been absent over the last year.

You can even use it to total how many products fall below a certain stock level!

Basically, the COUNTIF function is perfect for quickly tallying up conditional counts for teams with expansive data sets.

The AVERAGEIF function calculates the average values in a range that meet a specific condition.

Want to find the average sale amount for repeat customers? Or maybe the average monthly revenue for products that exceeded a certain sales threshold?

The AVERAGEIF function can calculate the value for you in seconds!

This function is a surprisingly simple but powerful way to instantly gain deeper insights into your data, without manually filtering anything.

Read more: How to create checkboxes in Google Sheets?

In Google Sheets, a function's syntax essentially refers to the format you must follow when writing a formula to ensure your formula works properly.

The syntax always includes the function name, required arguments (data you want to calculate), and optional arguments in the correct order.

The syntax of the SUMIF function is: SUMIF(range, criteria, [sum_range])

Let’s break down this syntax:

• range: The group of cells you want to evaluate.
• criteria: The condition determining which cells to include (number, text, cell reference, etc).
• [sum_range] (optional): The actual cells to sum. Note: If you don’t put anything in, Google Sheets will just sum the values within the range.
• The syntax of the COUNTIF function is: COUNTIF(range, criteria)

The breakdown of this syntax is:

• range: The group of cells.
• criteria: The condition that tells Google Sheets which cells to count (numbers, text, expressions, cell references, etc).
• The syntax of the AVERAGEIF function is: AVERAGEIF(criteria_range, criterion, [average_range])

Here is the breakdown:

• criteria_range: The range of cells you want to apply the condition to.
• criterion: The rule or condition that determines which cells to average.
• [average_range] (optional) – The actual values to average. Note: If you don’t put anything in, Google Sheets will simply average the values in criteria_range.

Read more: How to master VLOOKUP in Google Sheets for beginners?

These condition-based functions inside Google Sheets can be used for many small business applications.

From tracking sales performance, managing budgets, or analyzing customer data, these functions are essential for adding, counting, or averaging your business data!

Now that you know what each function is, it’s important to share how you can use all three functions to sort, manage, and analyze your data effectively in seconds.

If you're managing expenses, sales, inventory, or performance data, the SUMIF function is a lifesaver, letting you quickly add up values based on specific conditions!

Here’s how you can use the SUMIF function in Google Sheets:

1. Click the cell where you want the total sum to appear.
2. Type the SUMIF formula using this format: =SUMIF(range, criteria, [sum_range])
3. Replace (range, criteria, [sum_range]) with your spreadsheet data.

Suppose you have a list of product categories in column A and a list of sales revenue in column B, and you want to find the sum total sales for the category ‘Accessories.’

Your formula would be: =SUMIF(A2:A20, "Accessories", B2:B20)

Google Sheets will then add up all values in B2:B20 where any cell in A2:A20 equals ‘Accessories’.

To make your formula even more dynamic, you can also use a cell reference in your formula for your category, for example: =SUMIF(A2:A20, E1, B2:B20)

For this example, if E1 contains the text ‘Accessories’, Google Sheets will use that as your condition.

This function is also helpful for teams needing to total sales amounts made by specific sales reps.

For example, if you had a list of sales reps in column A and their sales amounts in column B and you want to sum the sales made by ‘Jason’, your formula would look like this:

=SUMIF(A2:A10, "Jason", B2:B10)

This essentially adds up the values in B2:B10 only where A2:A10 equals ‘Jason’.

Are you a small business owner looking to track sales, customers, or inventory patterns?

COUNTIF is a powerful function that helps you quickly identify how often certain conditions (such as customer orders) are met in your data.

Here’s how you can use the COUNTIF function in Google Sheets:

1. Click the cell where you want the total count to appear.
2. Enter the COUNTIF formula using this structure: =COUNTIF(range, criteria).
3. Replace (range, criteria) with your data.
4. For example, let's say you wanted to track all your customer orders in column A, then count how many orders exceeded $500.
5. Your formula would look like this: =COUNTIF(A2:A20, ">500")

This complete formula essentially tells Google Sheets to count how many values in cells A2 to A20 are greater than $500.

Remember, the range relates to the cells you want to check, and the criteria is the condition each cell must meet (e.g., ‘Completed’, ‘>500’, or a specific product name).

This function also works if you want to reference another cell for your criteria. For example:

=COUNTIF(A2:A20, ">" & D1)

Basically, this means that if cell D1 contains the number 500, the formula will work the same, making your sheet even more dynamic!

The AVERAGEIF function essentially calculates the average values that meet a specific criteria.

This is the perfect solution for small teams looking to quickly analyze trends, spot customer behavior, or track performance without manually sorting through data.

Here’s how you can use the AVERAGEIF function in Google Sheets:

1. Click the cell where you want the average to appear.
2. Type the AVERAGEIF formula using this format: =AVERAGEIF(criteria_range, criterion, [average_range])
3. Change (criteria_range, criterion, [average_range]) with your spreadsheet data.
4. For example, let’s say you want to track your monthly customer spending in column B and find the average spending of Gold-tier customers.
5. Your formula would be: =AVERAGEIF(A2:A20, "Gold", B2:B20)
6. This formula tells Google Sheets to average the values in B2:B20 where A2:A20 equals "Gold".

• You could also use it to find the average order value from customers who placed more than 3 orders (column A = number of orders, column B = total purchase).
• In this case, your formula would look like this:  =AVERAGEIF(A2:A15, ">3", B2:B15)
• This essentially averages the values in B2:B15 where A2:A15 is greater than 3.

And that’s how easy it is to use the SUMIF, COUNTIF, and AVERAGEIF functions inside Google Sheets!

As a small business owner myself, I get it: Your knowledge is limited, your time is sparse, and frankly, so is your margin for error!

This is why SUMIF, COUNTIF, and AVERAGEIF functions are so powerful for small businesses!

Once you’ve wrapped your head around how to use these functions, I promise they’ll become integral tools in your daily business operations.

These Google Sheets functions let you:

• Quickly total your sales by product, location, or team member (SUMIF)
• Track customer behaviors like repeat purchases or cancellations (COUNTIF)
• Spot performance trends like average order value or spend per customer tier (AVERAGEIF)

Instead of manually filtering and calculating data, these game-changing functions allow you to analyze your business in real time!

If you want to stay on top of your small business inside Google Sheets without spending hours sorting and filtering your data, try these condition-based Google Sheets functions!

Read more about how you can effectively manage your small business using my three Google Sheets business toolkits here.