SUMPRODUCT Differences: Excel vs. Google Sheets

The core purpose of the SUMPRODUCT function remains consistent across both Excel and Google Sheets. It multiplies corresponding items from two or more arrays and then sums those products.

However, the usage of the SUMPRODUCT function differs slightly between Excel and Google Sheets, particularly when applying conditions.

In addition, the array formula capability of SUMPRODUCT in Excel is exemplary, especially in Excel legacy formulas. It can be utilized to iterate over each value in an array in certain calculations. I’ll provide an example in the later part of this tutorial.

Google Sheets SUMPRODUCT doesn’t have this functionality. However, it’s no longer necessary, after LAMBDAS.

Boolean Value Handling: The Core Reason for SUMPRODUCT Usage Differences

Let’s first understand how SUMPRODUCT handles Boolean TRUE and FALSE values in multiplication. This will help us grasp the usage differences.

Suppose we have the following values in cells A1:B3 in both Excel and Google Sheets:

TRUE10
FALSE15
TRUE20

The syntax of the SUMPRODUCT function is SUMPRODUCT(array1, [array2, …]).

The following formula in Google Sheets will return 30, whereas in Excel, it will return 0:

=SUMPRODUCT(A1:A3, B1:B3)

In Google Sheets, SUMPRODUCT reads or assigns 1 to TRUE and 0 to FALSE. So, the total of 1*10 + 0*15 + 1*20 will be 30.

In Excel, you need to coerce Boolean values into their numeric equivalents using one of the techniques below:

  • double dash: =SUMPRODUCT(--A1:A3, --B1:B3)
  • multiplying: =SUMPRODUCT(A1:A3 * 1, B1:B3 * 1) or =SUMPRODUCT(A1:A3 * B1:B3)

When applying conditions to a range, for example, A1:A100=”Apple”, the formula returns TRUE or FALSE. This causes issues in Excel SUMPRODUCT, not in Google Sheets. The above methods address this in Excel. Here is a real-life example.

Example

Columns A to D contain Product, Region, Quarter, and Sales. The data range is A2:D9.

How to return the total sales of “Product A” in the “South” region?

In Google Sheets, you can use the following formula:

=SUMPRODUCT(A2:A9="Product A", B2:B9="South", D2:D9)

In Excel, you have a few options:

=SUMPRODUCT((A2:A9="Product A")*1, (B2:B9="South")*1, D2:D9)
=SUMPRODUCT((A2:A9="Product A")*(B2:B9="South"), D2:D9)
=SUMPRODUCT((A2:A9="Product A")*(B2:B9="South")*(D2:D9))

All these formulas will work in Google Sheets as well.

SUMPRODUCT Differences: Excel vs. Google Sheets

That is the difference between SUMPRODUCT in Excel and Google Sheets.

The Role of SUMPRODUCT in Legacy Array Formulas in Excel

The above formulas return the total sales of “Product A” in the “South” region. How do we exclude rows in the range that are hidden in this calculation?

Currently, there are no hidden rows. However, if I hide a row, I want the formulas to return the total of visible rows.

In older versions of Excel, you can use the following formula:

=SUMPRODUCT((A2:A9="Product A")*(B2:B9="South")*(SUBTOTAL(103, OFFSET(A2, ROW(A2:A9)-ROW(A2), 0)))*(D2:D9))

The formula is the same, but there is a new array included which is SUBTOTAL(103, OFFSET(A2, ROW(A2:A9)-ROW(A2), 0)).

What does this formula do?

It copies =SUBTOTAL(103, A2) to each row. It returns 1 in each visible row and 0 in each hidden row wherever a value is present in A2:A9. The function number 103 in SUBTOTAL is equal to COUNTA but skips hidden values.

This formula will work in Excel for Microsoft 365 as well but not in Google Sheets. In Google Sheets, you can replace the above-highlighted part with the following lambda function:

MAP(A2:A9, LAMBDA(r, SUBTOTAL(103, r)))

This will also work in Excel for Microsoft 365.

Conclusion

Understanding the nuances of how Excel and Google Sheets handle SUMPRODUCT, especially with Boolean values and array formulas, ensures your spreadsheets function smoothly on both platforms, maximizing the utility of this powerful function.

Resources

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

Days Between Weekday Names in Excel and Google Sheets

There isn't a specific function to calculate the number of days between weekday names...

Display Month Names Only at Month Start (Excel & Google Sheets)

This tutorial explains how to display month names only at the start of each...

SUMIFS with XLOOKUP in Excel and Google Sheets

SUMIFS and SUMIF are infamous for producing an "argument must be a range" error...

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.