sumproduct and transpose

[Gordana Godzo]

Can sumproduct work combining rows and columns if nested with transpose? For
example:

SUMPRODUCT(--('[CC-Analysis0512.xls]SALA'!$B$5:$B$46)=B15,TRANSPOSE('[CC-Analysis0512.xls]SALA'!$D$3:$AR$3)=B12,'[CC-Analysis0512.xls]SALA'!$D$5:$D$46)
or maybe

SUMPRODUCT(('[CC-Analysis0512.xls]SALA'!$B$5:$B$46=B15)*(TRANSPOSE('[CC-Analysis0512.xls]SALA'!$D$3:$AR$3)=$B$12)*'[CC-Analysis0512.xls]SALA'!$D$5:$D$46)

With the first formula I get N/A, and with the second 0, although it should
return some value.

Thank you

Gordana Godzo
Head of Controlling & Budgeting
TITAN Group - Cementarnica "USJE" AD Skopje
Prvomajska bb, 1000 Skopje
tel: +389 2 2786 138
fax: +389 2 2782 535
e-mail: (e-mail address removed)

 

[Kevin Vaughn]

I admit I don't know, but have you tried transposing the last part of the
formula. In both examples you give, these are not transposed so I would try
that first.

 

[Domenic]

The ranges need to be the same size. Try...

=SUMPRODUCT(--('[CC-Analysis0512.xls]SALA'!$B$5:$B$46=B15),--(TRANSPOSE('
[CC-Analysis0512.xls]SALA'!$D$3:$AS$3)=B12),'[CC-Analysis0512.xls]SALA'!$
D$5:$D$46)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Bob Phillips]

You can, but a few points.

Even transposed, ranges must be the same size, so it should be D3:AS3
The double unary doesn't work with TRANSPOSED data, you need the star
operator
It needs to be array entered

SUMPRODUCT(('[CC-Analysis0512.xls]SALA'!$B$5:$B$46=B15)*(TRANSPOSE('[CC-Anal
ysis0512.xls]SALA'!$D$3:$AS$3)=$B$12)*'[CC-Analysis0512.xls]SALA'!$D$5:$D$46
)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

Can sumproduct work combining rows and columns if nested with transpose? For

example:

SUMPRODUCT(--('[CC-Analysis0512.xls]SALA'!$B$5:$B$46)=B15,TRANSPOSE('[CC-Ana
lysis0512.xls]SALA'!$D$3:$AR$3)=B12,'[CC-Analysis0512.xls]SALA'!$D$5:$D$46)
or maybe

SUMPRODUCT(('[CC-Analysis0512.xls]SALA'!$B$5:$B$46=B15)*(TRANSPOSE('[CC-Anal
ysis0512.xls]SALA'!$D$3:$AR$3)=$B$12)*'[CC-Analysis0512.xls]SALA'!$D$5:$D$46
)

With the first formula I get N/A, and with the second 0, although it should
return some value.

Thank you

Gordana Godzo
Head of Controlling & Budgeting
TITAN Group - Cementarnica "USJE" AD Skopje
Prvomajska bb, 1000 Skopje
tel: +389 2 2786 138
fax: +389 2 2782 535
e-mail: (e-mail address removed)