Description: The example is quite self-explaining. Generate the following data set, beginning at cell B4 (I'm sure it "works" everywhere): Date 1 06.10.21 00:00 2 06.10.21 00:00 3 06.10.21 00:00 4 06.10.21 00:00 5 06.10.21 00:00 6 06.10.21 00:00 7 06.10.21 00:00 8 06.10.21 00:00 9 06.10.21 00:00 Enter then the same date into another cell (e.g. F5), and the following formula (I run the German version of LibreOffice): =SUMME( UND( MONAT(F5)=MONAT(C5:C13));TAG(F5)=TAG(C5:C13) ) The result is: 10 I had expected: 9 The formula should sum up the cells in which the month is "10" and the date is "6". As you see, there are only 9 entries in the data block, all other cells except the cell with the "check-date" and the formula are empty. Together with the "check-date" the sum is 10, but this is not the expected result. I'm using this type of formula to do some simple searches in rows, columns or even matrixes. Runs fine in EXCEL. Steps to Reproduce: 1. Fill the cells according description 2. Remember to make the formula a "matrix" 3. Actual Results: 10 Expected Results: 9 Reproducible: Always User Profile Reset: No Additional Info: Version: 7.1.4.2 / LibreOffice Community Build ID: a529a4fab45b75fefc5b6226684193eb000654f6 CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: de-DE (de_DE.UTF-8); UI: de-DE Calc: threaded
Created attachment 176028 [details] .ods file with suspected error
I confirm the 10 result already in LO 4.2.8 version. But in Excel 2007 I see result 1 in the cell with formula! I'm not sure what should be right here. Eike, could you please look at it?
On pc Debian x86-64 with master sources updated today, I could reproduce this. If you change cell A32 to "05/10/21", you got "1" in D32 But if you change cell A32 to "05/09/21", you got "0" in D32 and if you change cell A32 to "06/09/21", you got "9" in D32 I'd definitely say there's something wrong here.
Seems the issue if with AND() in array {=SUM( (MONTH(C32)=MONTH(C5:C13))*(DAY(C32)=DAY(C5:C13) ))} avoiding the use of AND works fine.
(In reply to m.a.riosv from comment #4) > Seems the issue if with AND() in array > {=SUM( (MONTH(C32)=MONTH(C5:C13))*(DAY(C32)=DAY(C5:C13) ))} > avoiding the use of AND works fine. Good find! I don't know how to find methods related to matrix. It would be great to have some refactoring instead of trying to search in files like: ./source/core/tool/interpr1.cxx ./source/core/tool/interpr2.cxx ./source/core/tool/interpr3.cxx ./source/core/tool/interpr4.cxx ./source/core/tool/interpr5.cxx ./source/core/tool/interpr6.cxx ./source/core/tool/interpr7.cxx ./source/core/tool/interpr8.cxx or at least a small readme in top of each of this kind of file which have between 550 and 10000 lines!
(In reply to Julien Nabet from comment #5) > Good find! I don't know how to find methods related to matrix. It would be > great to have some refactoring instead of trying to search in files like: > ./source/core/tool/interpr1.cxx > ./source/core/tool/interpr2.cxx > ./source/core/tool/interpr3.cxx > ./source/core/tool/interpr4.cxx > ./source/core/tool/interpr5.cxx > ./source/core/tool/interpr6.cxx > ./source/core/tool/interpr7.cxx > ./source/core/tool/interpr8.cxx > or at least a small readme in top of each of this kind of file which have > between 550 and 10000 lines! Agree * 1000!
I can't find now, but I think time ago something about logical functions in matrix or using them inside sumproduct was reported, maybe @Erak remember something.
First, the expression =SUM( AND( MONTH(C32)=MONTH(C5:C13));DAY(C32)=DAY(C5:C13) ) probably was intended as =SUM( AND( MONTH(C32)=MONTH(C5:C13);DAY(C32)=DAY(C5:C13)) ) instead (note that in the former AND() receives one argument and in the latter two arguments). Second, there seems to be a misconception of AND(), it never returns an array/matrix (neither does OR()), but computes a logical AND of all arguments, including their matrix elements. That has always been the case and will not change. See also https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#AND Use SUM() as in comment 4 or =SUMPRODUCT(MONTH(C32)=MONTH(C5:C13);DAY(C32)=DAY(C5:C13)) as regular formula.