Apache OpenOffice (AOO) Bugzilla – Issue 63166
Summing multiple columns in Matrix formula doesn't work
Last modified: 2010-11-10 23:49:10 UTC
I try to sum a range inside a long table if the date matches a certain criterium. Say I have one column with the dates, and I am interested in the sum of the numbers Num1 and Num2 of all dates in a certain month. Now the matrix selector does work, but only the data in the first column is added; the other(s) is ignored. EXAMPLE: -------- Date Num1 Num2 01/01/06 4 5 01/02/06 4 5 01/03/06 4 5 01/04/06 4 5 01/05/06 4 5 01/06/06 4 5 01/07/06 4 5 01/08/06 4 5 01/09/06 4 5 01/10/06 4 5 01/11/06 4 5 01/12/06 4 5 01/13/06 4 5 01/14/06 4 5 01/15/06 4 5 01/16/06 4 5 01/17/06 4 5 01/18/06 4 5 01/19/06 4 5 01/20/06 4 5 01/21/06 4 5 01/22/06 4 5 01/23/06 4 5 01/24/06 4 5 01/25/06 4 5 01/26/06 4 5 01/27/06 4 5 01/28/06 4 5 01/29/06 4 5 01/30/06 4 5 01/31/06 4 5 02/01/06 4 5 02/02/06 4 5 02/03/06 4 5 02/04/06 4 5 02/05/06 4 5 02/06/06 4 5 Cell A40 contains 1 feb 2006 I can then insert the formula {SUM(IF(MONTH(A40)=MONTH(A1:A37);B1:C37))} and the result is 24, instead of the expected 54. I can also do {SUM(IF(MONTH(A40)=MONTH(A1:A37);B1:B37))} (24) {SUM(IF(MONTH(A40)=MONTH(A1:A37);C1:C37))} (30) so the principle of the function seems to work, however the adjacent column is not added at all. In MS excel it works fine, so there does not appear to be a bug in my logic.. :-)
Created attachment 34868 [details] this is a clear example of the described bug
if it didn't exhibit the same mistake, you could use a conditional sum as an array formula, {=SUM((MONTH(A40)=MONTH(A2:A38))*$B$2:$C$38)} (shorter); confirmed with 680_m158 on WinXP Pro SP2
duplicate to Issue 46681 *** This issue has been marked as a duplicate of 46681 ***
closed double
Created attachment 73867