Why does SUM work with multiple sheet selection, but SUMPRODUCT doesn't?

[larryf]

Hi all,
Why is it that I can SUM a cell (or cells) across multiple sheets, but not use SUMPRODUCT in the same fashion?

In the sample file, attached, imagine that a stock brokerage account contains holdings in three different companies. The account's holdings are each on a worksheet dedicated to that company's stock. The sheet "Total Acct Value" is where a summary of the account is kept.

On sheet "Total Acct Value", in cell B1, the formula =SUM('Stock 1:Stock 3'!B1) works just fine. On the other hand, in cell B2, the formula SUMPRODUCT('Stock 1:Stock 3'!B1,'Stock 1:Stock 3'!B2) results in a #VALUE error. My questions are:

• Why does SUM work and SUMPRODUCT doesn't?
• It seems that Excel doesn't consider a multiple sheet selection to be an array (because it's 3-dimensional rather than 2-dimensional, maybe)? If not an array (as the term is used in 'new' Excel, what is a 1x1xN (where N is the number of sheets selected) set of cells considered to be? A range? Something else?
• The sample file contains a simplified version of a problem I'm having with a much more complex workbook, but the essence is the same. I want to extract 2 bits of data from multiple worksheets, multiply them together, then get a grand total on a summary sheet. Doing the intermediate math on the subordinate sheets and then summing on the summary sheet seems inelegant. Is there a better way to do this?

Thanks in advance for your help, smart people!

 

[p45cal]

Is there a better way to do this?

Try:

=LET(NumberOfShares,VSTACK('Stock 1:Stock 3'!B1),PricePerShare,VSTACK('Stock 1:Stock 3'!B2),SUMPRODUCT(NumberOfShares,PricePerShare))

or simpler:

=SUMPRODUCT(VSTACK('Stock 1:Stock 3'!B1),VSTACK('Stock 1:Stock 3'!B2))

 

[larryf]

Try:

=LET(NumberOfShares,VSTACK('Stock 1:Stock 3'!B1),PricePerShare,VSTACK('Stock 1:Stock 3'!B2),SUMPRODUCT(NumberOfShares,PricePerShare))

or simpler:

=SUMPRODUCT(VSTACK('Stock 1:Stock 3'!B1),VSTACK('Stock 1:Stock 3'!B2))

Thanks, p45cal! Very nice solution to my problem.

 

[pecoflyer]

Why does SUM work and SUMPRODUCT doesn't?

SUMPRODUCT does not support 3D references

 

[larryf]

SUMPRODUCT does not support 3D references

Thanks to that link I can see that now--that 3D references are their own special data type in Excel and differ from arrays in terms of which functions they can be used with. I appreciate your help, pecoflyer!

 

[pecoflyer]

You're welcome

 

[Monty]

Hi all,
Why is it that I can SUM a cell (or cells) across multiple sheets, but not use SUMPRODUCT in the same fashion?

In the sample file, attached, imagine that a stock brokerage account contains holdings in three different companies. The account's holdings are each on a worksheet dedicated to that company's stock. The sheet "Total Acct Value" is where a summary of the account is kept.

On sheet "Total Acct Value", in cell B1, the formula =SUM('Stock 1:Stock 3'!B1) works just fine. On the other hand, in cell B2, the formula SUMPRODUCT('Stock 1:Stock 3'!B1,'Stock 1:Stock 3'!B2) results in a #VALUE error. My questions are:

• Why does SUM work and SUMPRODUCT doesn't?
• It seems that Excel doesn't consider a multiple sheet selection to be an array (because it's 3-dimensional rather than 2-dimensional, maybe)? If not an array (as the term is used in 'new' Excel, what is a 1x1xN (where N is the number of sheets selected) set of cells considered to be? A range? Something else?
• The sample file contains a simplified version of a problem I'm having with a much more complex workbook, but the essence is the same. I want to extract 2 bits of data from multiple worksheets, multiply them together, then get a grand total on a summary sheet. Doing the intermediate math on the subordinate sheets and then summing on the summary sheet seems inelegant. Is there a better way to do this?

Thanks in advance for your help, smart people!

The reason why `SUM` works across multiple sheets while `SUMPRODUCT` doesn't is because `SUM` can handle 3D references (across multiple sheets) directly, while `SUMPRODUCT` does not support this type of reference. `SUMPRODUCT` typically expects 1D or 2D arrays.

Jello Larryf

In Excel, a 1x1xN set of cells selected from multiple sheets is not considered an array in the same way as 2D arrays. It is more of a reference to individual cells rather than an array structure.

For your task of extracting data from multiple sheets, multiplying them, and then getting a grand total, using intermediate calculations on subordinate sheets might be a practical approach. Another option could involve using helper columns or cells on each subordinate sheet to perform the intermediate calculations, and then referring to those results in your summary sheet. This could enhance clarity and organization in your complex workbook.