report-openoffice-team team mailing list archive

[Erwin Olario <govvin@xxxxxxxxx>]

Public bug reported:


I have a table where I am trying to add values based on a column code criteria where I want to view the sum of values given a code.  In my spreadsheet (see attached file), values under are being summed up incorrectly, while it appears to be working fine for other columns.

The formula I use:
=sumif(Sheet1!$B2:$F8,"="&$B10,Sheet1!D2:D9)

I thought my error had to do with the use of a text string in the code
column, and the function may have been expecting an integer but after
changing the code column to numeric integers, there were less errors but
I'm still getting incorrect results.

The function behaves incorrectly when encountering 2 vs 20, 7 vs 70, or
with single digit numbers and the corresponding double-digit number that
starts with the same (1 and 10, 3 and 30, 4 and 40, etc.) The sumif
criteria works with strings and integers, but produces inconsistent
results when these numbers are found in a criteria range.

Note;
When I say string, I mean I enter the number as "'02" instead of just "2". The former is treated as numeric value, the latter a character string. 

I hope the attached spreadsheet below can help explain further.

Thanks.

** Affects: report-openoffice
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Report
OpenOffice Team, which is the registrant for Report OpenOffice.
https://bugs.launchpad.net/bugs/794352

Title:
  sumif adds values inconsistently

Status in OpenOffice.org Reporting Engine for OpenObject:
  New

Bug description:
  
  I have a table where I am trying to add values based on a column code criteria where I want to view the sum of values given a code.  In my spreadsheet (see attached file), values under are being summed up incorrectly, while it appears to be working fine for other columns.

  The formula I use:
  =sumif(Sheet1!$B2:$F8,"="&$B10,Sheet1!D2:D9)

  I thought my error had to do with the use of a text string in the code
  column, and the function may have been expecting an integer but after
  changing the code column to numeric integers, there were less errors
  but I'm still getting incorrect results.

  The function behaves incorrectly when encountering 2 vs 20, 7 vs 70,
  or with single digit numbers and the corresponding double-digit number
  that starts with the same (1 and 10, 3 and 30, 4 and 40, etc.) The
  sumif criteria works with strings and integers, but produces
  inconsistent results when these numbers are found in a criteria range.

  Note;
  When I say string, I mean I enter the number as "'02" instead of just "2". The former is treated as numeric value, the latter a character string. 

  I hope the attached spreadsheet below can help explain further.

  Thanks.