Every cell in your specified data range is applied with a single formula via **the ARRAYFORMULA function** in Google Sheets. By converting your formula into an array, the function enables you to create a single formula and apply it to several rows. We can use the **ARRAYFORMULA **function to sum in Google Sheets. In this article, I will demonstrate 5 simple examples of using the **ARRAYFORMULA **function to sum in Google Sheets.

**Table of Contents**hide

**A Sample of Practice Spreadsheet**

You can download the free Google Sheet from here and practice independently.

**5 Simple Examples to Sum Using ARRAYFORMULA in Google Sheets**

First, we will use the combination of the **ARRAYFORMULA **and **SUM **functions to sum in Google Sheets. Then we will use the **ARRAYFORMULA **function in two ways to get the sum of multiple columns. Then we will use **ARRAYFORMULA **to sum based on single criteria and multiple criteria in Google Sheets.

**1. ****Combining ARRAYFORMULA and SUM Functions**

In Google Sheets, we may sum using the **ARRAYFORMULA **and **SUM **functions together. Let’s say we have a list of products with quantity and unit prices and want to determine the total amount of sales.

**Steps:**

- First, select a cell where you want to display the total sales of the products. We selected
**Cell D11**.

- Now, type the formula below and press the
**Enter**button-

`=ARRAYFORMULA(SUM(C5:C9*D5:D9))`

**Formula Breakdown**

**SUM(C5:C9*D5:D9)**

It will sum up the results of **C5:C9*D5:D9**.

**ARRAYFORMULA(SUM(C5:C9*D5:D9))**

It will return the non-array operations with arrays.

- Thus, it will display the total amount of sales in your desired cell.

**2. ****Using ARRAYFORMULA to Sum Multiple Columns**

We can sum multiple columns using the **ARRAYFORMULA **function in Google Sheets. This is a basic formula, to sum up, multiple columns. When there are not many columns to sum, this performs wonderfully.

**Steps**:

- First, choose a cell where you want to show the sum of total sales. We chose
**Cell F6**.

- Now, enter the following formula and hit the
**Enter**button-

`=ARRAYFORMULA(C6:C10+D6:D10+E6:E10)`

- It will show the total sales of each product right away when you hit the
**Enter**button.

**3. ****Applying MMULT Function to Sum Multiple Columns**

We can apply **the MMULT function** along with the **ARRAYFORMULA **function to sum multiple columns in Google Sheets. The **MMULT **function in Google Sheets is likely the most effective approach to calculating the sum of many columns.

**Steps:**

- To begin, select a cell where you want to display the sum of total sales. In our case, we selected
**Cell F6**.

- Now, type the following formula and press
**Enter**–

`=MMULT(C6:E10,TRANSPOSE(ARRAYFORMULA(COLUMN(C6:E10)^0)))`

**Formula Breakdown**

**COLUMN(C6:E10)**

First, it will return the column number of your data range.

**ARRAYFORMULA(COLUMN(C6:E10)^0)**

Then, the **ARRAYFORMULA **function will return the non-array functions into an array.

**TRANSPOSE(ARRAYFORMULA(COLUMN(C6:E10)^0))**

After that, **the TRANSPOSE function **will transpose the rows and columns of the array.

**MMULT(C6:E10,TRANSPOSE(ARRAYFORMULA(COLUMN(C6:E10)^0)))**

The **MMULT **function then determines the matrix product of two supplied matrices in the form of arrays or ranges.

- The total sales of each product will be shown as soon as you hit the
**Enter**button.

**4. ****Coupling SUMIF and ARRAYFORMULA**

In Google Sheets, we may sum by combining the **SUMIF **and **ARRAYFORMULA **functions. When summing numbers based on one criterion, **SUMIF **is utilized.

**Steps:**

- We want to know the total sales of Blackberries and Grapes. To do this, first select a cell where you want to display the output. We selected
**Cell F12**.

- Now, type the formula below and hit the
**Enter**key-

`=ARRAYFORMULA(SUMIF(B6:B10,E12:E13,F6:F10))`

**Formula Breakdown**

**SUMIF(B6:B10,E12:E13,F6:F10)**

It returns the conditional sum across **Cell F6:F10** based on the condition given by **Cell E12:E13 **across the range **Cell F6:F10**.

**ARRAYFORMULA(SUMIF(B6:B10,E12:E13,F6:F10))**

It will return the non-array operations into an array.

- Thus, it will display the total sales of
**Blackberries**and**Grapes**.

**5. ****Merging SUMIFS and ARRAYFORMULA**

To sum up, in Google Sheets, we may also combine the **SUMIFS **and **ARRAYFORMULA **functions. Summarizing data in a range based on several criteria is the goal of the **SUMIFS **function.

**Steps:**

- We’re interested in seeing how many
**Blackberries**are sold overall by**Shop A**. To display the output in a cell, you must first select it. We chose**Cell F12**.

- Now, enter the following formula and hit
**Enter**–

`=ARRAYFORMULA(SUMIFS(G6:G10,C6:C10,E12,B6:B10,F12))`

**Formula Breakdown**

**SUMIFS(G6:G10,C6:C10,E12,B6:B10,F12)**

Based on a variety of criteria, it will give the sum of a range.

**ARRAYFORMULA(SUMIFS(G6:G10,C6:C10,E12,B6:B10,F12))**

It will give back the non-array operations into an array.

- Thus, it will show the total sales of
**Blackberries**from**Shop A**.

**What to Do If ARRAYFORMULA Is Not Working in Google Sheets?**

The **ARRAYFORMULA **function in Google Sheets may be used to sum and return values for the sum over all rows. However, if you use the **ARRAYFORMULA **incorrectly, it can display an error, or the value might only be entered into one row rather than all the rows. Here, I’ll give you an example.

**Steps:**

- We want to get the total sales of each product. So, we selected
**Cell F6**.

- Now, if you enter the formula below to sum the multiple columns and press
**Enter**–

`=ARRAYFORMULA(SUM(C6:E6))`

- It will show the total sales for one row only. But we wanted to know the total sales for every product.

- To achieve this, first clear the cell. Then enter the formula below and press
**Enter**–

`=ARRAYFORMULA(C6:C10+D6:D10+E6:E10)`

- Thus, it will display the total sales for each product.

**Conclusion**

In this article, I have shown 5 simple examples, to sum up by using the **ARRAYFORMULA **function in Google Sheets. I have also shown what to do if the **ARRAYFORMULA **function is not working properly in Google Sheets. Please feel free to ask any questions or comment on any ideas in the comment section below. To explore more, visit **Officewheel.com**.