Tag: google sheets sumifs

Google Sheets SUMIFS Function

This post will guide you how to use Google Sheets SUMIFS function with syntax and examples in Google Spreadsheet.

Description

The Google Sheets SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. You can use the SUMIFS function to sum cells that based on the dates, numbers or texts based on one criteria in Google Sheets. And it also supports logical operators and wildcards.

The SUMIFS function will extend the capabilities of the SUM function by giving you a choice that you want only sum those values that meet a single or multiple criteria.

The SUMIFS function is a build-in function in Google Sheets and it is categorized as a Math and Trigonometry Function.

Syntax

The syntax of the SUMIFS function is as below:

=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where the SUMIFS function arguments are:

  • sum_range -This is a required argument. The actual cells to sum together.
  • criteria_range1 -This is a required argument. The range of cells that you want to apply criteria1 against.
  • criteria1 – This is a required argument. The criteria to use on criteria_range1.so this criteria defines which cells in criteria_range1 will be applied.
  • [criteria_range2, criteria2] -This is an optional argument. It will be specified additional ranges and their associated criteria. Criteria2 will be applied against criteria_range2 and so on. It can be up to 127 criteria.

Note:

  • Any criteria that include logical or math symbols must be enclosed in double quotation marks.
  • The numeric criteria can be supplied without quotation marks.
  • You can use the wildcard characters in the criteria argument.
  • You can use multiple operators in the criteria argument.

Google Sheets SUMIFS Function Examples

The below examples will show you how to use Google Sheets SUMIFS Function to sum the values in a range of cells based on multiple criteria.

Suppose that you have a sales table as shown below:

Name Product Region Sales
Tom Excel North $210
Tom Word North $230
Tom Access North $190
Jenny Excel North $150
Jenny Word North $240
Jeff Excel East $140
Jeff Word East $340

1# to sum of the sales of the product “Excel” in region “North“, enter the following formula in Cell E1.

=SUMIFS(D2:D8,B2:B8,"Excel",C2:C8,"North")

 

Average Of Numbers With Multiple Criteria in Google Sheets

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria in google sheets? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter of seconds? Then congratulations because you have just landed on the right article.

In this article, you will get to know the easiest way to calculate the average of the numbers with respect to multiple criteria in no time in google sheets.

So read this article till the end, and let’s dive into it;

Average cells based on multiple criteria in google sheets1

General Formula in google sheets

=AVERAGEIFS(values,range1,criteria1,range2,criteria2)

Explanations for Syntax:

Before learning how to use the above formula, let’s first understand the role of each syntax in quickly calculating the average of the numbers concerning multiple criteria in google sheets.

  • AVERAGEIFS: In google sheets, this function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main function of this symbol is to group the elements.
  • Range: It represents the input value from your worksheet.
  • Criteria: The specific criteria or values used to calculate the average.

Explanation

You can use the AVERAGEIFS function to average numbers based on multiple criteria in google sheets. In the example, the formula in H2  is as follows:

=AVERAGEIFS(sales,product,E2,region,F2)

Average cells based on multiple criteria in google sheets1

Where “sales” (C2:C9), “product” (A2:A9), and “sales” (C2:C9) are named ranges.

The AVERAGEIFS function, like the COUNTIFS and SUMIFS functions, is intended to handle multiple criteria that are entered in [range, criteria] pairs. AVERAGEIFS’s behavior is fully automatic as long as this information is provided correctly.

In the example, we have product values in column E and region values in column F. We use these values directly by using cell references as criteria.

The first argument holds the range of values to average:sales range

To limit the calculation by product, we provide:product, E2

We use the following formula to limit the calculation by region:region, F2

The answer in cell H2 is 396:

Now, let’s go to the 2nd example in which we want to average the product of “excel” and the sales greater than 300. Please follow these instructions:

Enter the formula given below into a blank cell:

=AVERAGEIFS(sales,product,E2,sales,F2)

Average cells based on multiple criteria in google sheets1

(product range is the data that contains the criteria1, sales range is the range which we want to calculate the average, E2 and F2 are the criteria1 and criteria 2), then press the Enter key to get the desired result. Take a look at this screenshot:

Note: If you need more than two criteria, add the criteria ranges and criteria you require as follows:

=AVERAGEIFS(sales,product,E2,region,F2,sales,G2)

Average cells based on multiple criteria in google sheets1

Note: the first criteria range is product and E2, the second criteria range is region and F2 and the third criteria is sales and G2 and the range you want to average the values is the criteria, sales range.

For Better understanding, we are adding up the 3rd example as follows:

  • Consider the example below to calculate the average of numbers based on multiple criteria.
  • The image below will show the input values for Columns B, C, and D.
  • Next, enter the given formula in the formula bar section.
  • Finally, we’ll see the result in cell H3.

Average cells based on multiple criteria in google sheets1

Summary

This article explains how to use the formulas in google sheets to calculate the average of numbers with multiple criteria. I hope you found this article interesting. If you have any ideas, please share them with us.

Related Functions

  • Google Sheets AVERAGEIFS function
    The Google Sheets AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
  • google Sheets COUNTIFS function
    The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
  • Google Sheets SUMIFS Function
    The Google Sheets SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…