software

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 amp; Budgeting
TITAN Group - Cementarnica quot;USJEquot; AD Skopje
Prvomajska bb, 1000 Skopje
tel: 389 2 2786 138
fax: 389 2 2782 535
e-mail:
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.
--
Kevin Vaughnquot;Gordana Godzoquot; wrote:

gt; Can sumproduct work combining rows and columns if nested with transpose? For
gt; example:
gt;
gt; 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)
gt; or maybe
gt;
gt; 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)
gt;
gt; With the first formula I get N/A, and with the second 0, although it should
gt; return some value.
gt;
gt; Thank you
gt;
gt; Gordana Godzo
gt; Head of Controlling amp; Budgeting
gt; TITAN Group - Cementarnica quot;USJEquot; AD Skopje
gt; Prvomajska bb, 1000 Skopje
gt; tel: 389 2 2786 138
gt; fax: 389 2 2782 535
gt; e-mail:
gt;
gt;
gt;

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!

In article gt;,
quot;Gordana Godzoquot; gt; wrote:

gt; Can sumproduct work combining rows and columns if nested with transpose? For
gt; example:
gt;
gt; SUMPRODUCT(--('[CC-Analysis0512.xls]SALA'!$B$5:$B$46)=B15,TRANSPOSE('[CC-Analy
gt; sis0512.xls]SALA'!$D$3:$AR$3)=B12,'[CC-Analysis0512.xls]SALA'!$D$5:$D$46)
gt; or maybe
gt;
gt; SUMPRODUCT(('[CC-Analysis0512.xls]SALA'!$B$5:$B$46=B15)*(TRANSPOSE('[CC-Analys
gt; is0512.xls]SALA'!$D$3:$AR$3)=$B$12)*'[CC-Analysis0512.xls]SALA'!$D$5:$D$46)
gt;
gt; With the first formula I get N/A, and with the second 0, although it should
gt; return some value.
gt;
gt; Thank you
gt;
gt; Gordana Godzo
gt; Head of Controlling amp; Budgeting
gt; TITAN Group - Cementarnica quot;USJEquot; AD Skopje
gt; Prvomajska bb, 1000 Skopje
gt; tel: 389 2 2786 138
gt; fax: 389 2 2782 535
gt; e-mail:

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)

quot;Gordana Godzoquot; gt; wrote in message
...
gt; Can sumproduct work combining rows and columns if nested with transpose?
For
gt; example:
gt;
gt;
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)
gt; or maybe
gt;
gt;
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
)
gt;
gt; With the first formula I get N/A, and with the second 0, although it
should
gt; return some value.
gt;
gt; Thank you
gt;
gt; Gordana Godzo
gt; Head of Controlling amp; Budgeting
gt; TITAN Group - Cementarnica quot;USJEquot; AD Skopje
gt; Prvomajska bb, 1000 Skopje
gt; tel: 389 2 2786 138
gt; fax: 389 2 2782 535
gt; e-mail:
gt;
gt;