Array Formula to Multiply Every Two Columns and Total in Google Sheets

Published on

This post explains how to write a dynamic array formula to multiply every two columns in Google Sheets. There are two solutions.

Where does this type of multiplication come into use?

If the (purchase or sell) prices of items keep changing, you may find this type of calculation worthy.

For example, see this sample data (purchase).

array formula to multiply every two columns - example
image # 1

There are five products in column A, and you have the break-up of their purchase.

Let’s take one item, i.e., “Product 1”. The total quantity of it is 5+10+10=25.

Instead of specifying 25 in cell C2, I have distributed them in three columns because their purchase prices were different every time.

In this case, to get the total purchase amount, we can use the following formula in cell B2.

OPTION # 1 (NON-ARRAY)

=C2*D2+E2*F2+G2*H2

Alternatively, we can use the MULTIPLY function as below.

OPTION # 2 (NON-ARRAY)

=multiply(C2,D2)+multiply(E2,F2)+multiply(G2,H2)

Array Formula to Multiply Every Two Columns and Total – Non Dynamic

I know you are familiar with these two types of formulas. But what about an array formula to multiply every two columns and total as above in Google Sheets.

Google Sheets offers the ArrayFormula function to convert such formulas to expand in rows.

Empty B2:B. Then try either of the below array formulas in cell B2 to multiply every two columns in the range and total them.

OPTION # 1 (ARRAY)

=ArrayFormula(IFERROR(1/(C2:C*D2:D+E2:E*F2:F+G2:G*H2:H)^-1))

OPTION # 2 (ARRAY)

=ArrayFormula(IFERROR(1/(multiply(C2:C,D2:D)+multiply(E2:E,F2:F)+multiply(G2:G,H2:H))^-1))

Please note that the highlighted parts are optional.

If the multiplication formula returns zeros, it converts them into errors and thus makes them blank.

So in an open column range, the highlighted parts make the output more reader-friendly.

In Excel in Office 365, we can use the LET function for that. But at present, Google Sheets doesn’t have that enticing function.

So we use the above workaround, which is popular among Google Sheets’ Product Experts.

The above formulas, of course, return an array result.

But no way as a dynamic array formula as you may require to add new columns within it manually.

Array Formula to Multiply Every Two Columns and Total – Dynamic

Generic Formula:

ArrayFormula(mmult(multiply(filter(Qty_Columns),filter(Price_coluns)),{1;1;1}))

Let’s follow the above generic (dynamic array) formula to multiply every two columns and total in Google Sheets.

There are two main parts and one sub-part.

  1. MULTIPLY.
    1. FILTER.
  2. MMULT.

Here are the details.

1. Virtual Arrays within the MULTIPLY Functioin

We can use virtual arrays within the MULTIPLY function to return a matrix output.

The following formula will return the output of C2:C*D2:D, E2:E*F2:F, and G2:G*H2:H in three columns.

STEP_1 FORMULA (NON DYNAMIC)

=ArrayFormula(multiply({C2:C,E2:E,G2:G},{D2:D,F2:F,H2:H}))
virtual range in MULTIPLY function
image # 2

We require a dynamic array formula to multiply every two columns in Google Sheets.

So instead of specifying column ranges as above, we require to use the whole range C2:H. The FILTER comes into use here.

1.1. Filtering Factors

The MULTIPLY function has two arguments, and they are factor1 and factor2.

Syntax: MULTIPLY(factor1, factor2)

factor1 – {C2:C,E2:E,G2:G}

factor2 – {D2:D,F2:F,H2:H}

We can use the FILTER function to extract the factors with the help of the ISODD and ISEVEN functions.

factor1- filter(C2:H,isodd(column(C1:H1)))

factor2 – filter(C2:H,ISEVEN(column(C1:H1)))

These help us to make our step_1 formula dynamic. Here it is.

STEP_1.1 FORMULA (DYNAMIC)

=ArrayFormula(multiply(filter(C2:H,isodd(column(C1:H1))),filter(C2:H,ISEVEN(column(C1:H1)))))

We have coded the dynamic array formula to multiply every two columns in Google Sheets. The totaling part is only left.

I mean, the outputs are in three columns. We require to total them. Let’s use MMULT for that.

2. MMULT – Total Multiplied Columns

We require just one more step to add the output row-wise.

For that purpose, we can try different functions that include QUERY, MMULT, DSUM, and SUMIF in Google Sheets. Here are two related links. 

Here I am using MMULT.

Syntax:- MMULT(matrix1, matrix2)

matrix1 – STEP_1.1 FORMULA (DYNAMIC)

matrix2 – =ArrayFormula(sequence(COLUMNS(C1:H1)/2)^0)

There are six columns involved in the multiplication calculation as the range is C1:H.

So the STEP_1.1 FORMULA (DYNAMIC) output will be in three columns. Please see image # 2 above.

Since STEP_1.1 FORMULA (DYNAMIC) is matrix1, we require to specify the array {1;1;1} as matrix2 in MMULT to add the columns.

That means if the outputs are in three columns, we require 1 column containing three rows. The SEQUENCE-based matrix2 formula dynamically returns that.

Here is the final formula (for cell B2).

=arrayformula(IFERROR( 1 / mmult(MULTIPLY(filter(C2:H,isodd(column(C1:H1))),filter(C2:H,ISEVEN(column(C1:H1)))),sequence(COLUMNS(C1:H1)/2)^0) ^ -1))

This way we can code an array formula to multiply every two columns and total in Google Sheets.

Additional Tips and Query Alternative

I know you might use the above formula in a different range rather than C2:H.

No issue, you can make the formula work by adjusting the range references in the formula.

But, you must make sure that the total number of columns in the multiplication range is even.

In the above formula, it’s 6 (C2:H), which satisfies this condition.

Here is an additional formula, and here also, the above condition is applicable.

If you want, you can replace the above solution in cell B2 with the following QUERY alternative. It is also dynamic!

=ArrayFormula(iferror(1/query(query({n(C2:H)},"Select "&regexreplace(REGEXREPLACE(textjoin("*",true,"Col"&sequence(1,columns(C1:H1))),"(.*?\*){2}","$0+"),"\*\+","+")),"offset 1",0)^-1))

Do you want to learn this formula too? If so, here are the quick tips.

We can shorten the above Query as below. The below-highlighted part (non-dynamic) replaces the above bold part (dynamic).

=ArrayFormula(iferror(1/query(query({n(C2:H)},"Select Col1*Col2+Col3*Col4+Col5*Col6"),"offset 1",0)^-1))

How to make the highlighted part dynamic? To learn that, you can follow this tutorial – How to Replace Every Nth Delimiter in Google Sheets.

That’s all. Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.