Created attachment 29739 [details]
Screen captures of Calc and Excel

I see the effect in the screenshots, but currently I have no possibility to
reproduce the bug. I did some quick tests with 2.0 (1.9.m125) German version WIN
XP: [680m125(Build8947)] and saw SUMPRODUCT working as described in HELP.

@ deanbond 
Pls. attach a small .xls that reproduces the problem

In a test document with confidential content (contributed by "deanbond" I found
in Z12:
=SUMMENPRODUKT(($A$8:$W$217=$Y12)*($E$8:$E$217=Z$9))
Someone with more knowledge shuld decide: That's a correct formula for EXCEL
_and_ OOo? I do not get any errormessage, so it seems to be correct.
To me it seems to be funny, that that formula has the result '210', if I use it
in an empty calc document in 'Z12'.

My test from "comments from rainerbielefeld Wed Sep 21 01:58:08 -0700 2005" was
with 2.0 (1.9.m125) German version WIN XP: [680m125(Build8947)]

Opening the document with 1.1.4 (German) WIN XP: [645m52 (Build 8824)], I get an
errormessage '#VALUE!' for that formula.

@ deanbond:
What formula do you read in 'Z12' using EXCEL?

Hi Rainer,

> =SUMMENPRODUKT(($A$8:$W$217=$Y12)*($E$8:$E$217=Z$9))
> Someone with more knowledge shuld decide: That's a correct formula for EXCEL
> _and_ OOo?

Yes, it is. OOo1.x didn't support it in its plain form though, you had
to enter it as a matrix/array formula. This changed with OOo1.9.x for
Excel compatibility.

> To me it seems to be funny, that that formula has the result '210', if I use it
> in an empty calc document in 'Z12'.

May look weird at the first impression, but is perfectly logical ;-)
Internally, ($A$8:$W$217=$Y12) and ($E$8:$E$217=Z$9) create two arrays
where each element is TRUE, the result of the comparison of an empty
cell with an empty cell. The arrays are multiplied element by element,
resulting in an array where each element has the value 1, and the
elements are summed up then.

As for the issue described by the submitter: without knowing the
original data there's nothing we could say. I guess the 0 is because the
comparisons produce results of FALSE, maybe because numbers are compared
with strings, but this is just a guess.

  Eike

Numbers are really numbers, no text, but:
In my test document in Area 'A8:W217' many cells are empty E8:X217 seems to
contain nothing, no numbers, no text, no blank.
Can this cause the "0,00"?

If you don't think that that's the reason, I recommend to put the testfile to
the disposal of "er@openoffice.org" and "Frank Stecher (Sun)" for further
evaluation; my knowledge here is very limited. 

@ deanbond 
Do you agree to mail your doc for confidential evaluation to "Frank Stecher
(Sun)" and "er@openoffice.org"

Received the document from the submitter. Basically this boils down to
a duplicate of issue 46681: in Excel, the one-dimensional result vector
of $E$8:$E$217=Z$9 is repeated as many times as it is needed to multiply
it with the two-dimensional result array of $A$8:$W$217=$Y15. In Calc,
the vector is not repeated, therefore the remaining columns are all
0 and the result of the multiplication is a vector of 0s too.

To achieve identical results in Excel and Calc, the formula could be
rewritten as =SUMPRODUCT(($F$8:$F$217=$Y14)*($E$8:$E$217=Z$9))
note the now one-dimensional array $F$8:$F$217 of the column that is of
interest here. Doing so would also increase speed of calculation since
only the relevant column would have to be evaluated instead of 23
columns of which 22 are not needed for the result.

  Eike


*** This issue has been marked as a duplicate of 46681 ***

Closing dup.