TRANSPOSE() Excel 2016 vs Excel 365

Aeneas,

we need a sample file to be able to help you.

Share SharePoint files or folders

Share OneDrive files and folders - Office Support

Andreas.

I created a file and stumbled across even more weird formula behaviour.

Anyway.
How do I share that file with you here? It seems like I can't attach one here. And I can't share the file outside of my organisation.

I can't share the file outside of my organisation.

Aeneas,

upload the file here

Microsoft Answers Community Public Request - Dropbox

Andreas.

http://e.anyoupin.cn/EData/?p=tools.ceshi.index/uploadFileForm

share file

Hi,

Please show us the formula you are using and its results in 365.

Here is a simple example, I set up. Prior to dynamic arrays it would have required Ctrl+Shift+Enter:

=SUMPRODUCT((A1:A10)*TRANSPOSE(IF(D1:M1="a",3,-1)))

Hi Andreas,

thanks for the link and for the willingness to help! :)
Upload works!

Cheers

Hi peiyezhu,

I uploaded the file! Thanks for the help! :)

Cheers

=SUMPRODUCT(INDIRECT(ADDRESS(ROW(A3),COLUMN(B2))&":"&ADDRESS(ROW(A7),COLUMN(B2))),INDIRECT(ADDRESS(ROW(A3),COLUMN(C2))&":"&ADDRESS(ROW(A7),COLUMN(C2))),TRANSPOSE(F3:J3))

This works in Excel 365 (not in Excel 2016) without dynamic arrays. Remove TRANSPOSE, and it doesn't work anymore - which is super weird.

Thank you for trying to help! :)

Cheers

This has nothing to do with TRANSPOSE, the bottom is here:

This works in Excel 2019 and before:

{=INDIRECT(ADDRESS(3,2)&":"&ADDRESS(7,2))}

This does not:

{=INDIRECT(ADDRESS(ROW(A3),2)&":"&ADDRESS(7,2))}

Therefore this works

L3: {=SUMPRODUCT(B3:B7,C3:C7,TRANSPOSE(F3:J3))}
L4: =SUMPRODUCT(B3:B7,C3:C7,D3:D7)
L5: =SUMPRODUCT(INDIRECT(ADDRESS(3,2)&":"&ADDRESS(7,2)),INDIRECT(ADDRESS(3,3)&":"&ADDRESS(7,3)),D3:D7)
L6: {=SUMPRODUCT(INDIRECT(ADDRESS(3,2)&":"&ADDRESS(7,2)),INDIRECT(ADDRESS(3,3)&":"&ADDRESS(7,3)),TRANSPOSE(F3:J3))}

All other variants in your file do not.

Why use INDIRECT and ADDRESS? Even that looks overly complicated.

Can you give us an example of how to use this with real data? So we can see what should be the sense of that construct.

EDIT:

Maybe this example is helpful:

H6: =MATCH(G6,$B$3:$B$7)
H7:  =MATCH(G7,$B$3:$B$7)

I6: =SUMPRODUCT(INDEX(B3:B7,H6):INDEX(B3:B7,H7),INDEX(C3:C7,H6):INDEX(C3:C7,H7),INDEX(D3:D7,H6):INDEX(D3:D7,H7))
I7: {=SUMPRODUCT(INDEX(B3:B7,H6):INDEX(B3:B7,H7),INDEX(C3:C7,H6):INDEX(C3:C7,H7),TRANSPOSE(INDEX(F3:J3,H6):INDEX(F3:J3,H7)))}

Andreas.

Hi,

I ran it in Excel 2013 and the problem lay in the handling of the internal portion of the INDIRECT functions. Here is an alternate solution that works and my work for you:

=SUMPRODUCT(OFFSET(B3,0,0,SUM(ROW(A7)-ROW(A3)+1)),OFFSET(C3,0,0,SUM(ROW(A7)-ROW(A3)+1)),TRANSPOSE(F3:J3))

If you remove TRANSPOSE you also need to match parenthesis.

===============================

I found a solution for your formula:

=SUMPRODUCT(INDIRECT(ADDRESS(SUM(ROW(A3)),COLUMN(B2))&":"&ADDRESS(SUM(ROW(A7)),COLUMN(B2))),INDIRECT(ADDRESS(SUM(ROW(A3)),COLUMN(C2))&":"&ADDRESS(SUM(ROW(A7)),COLUMN(C2))),TRANSPOSE(F3:J3))

Add four SUMs