Bug #6580

SumifFormulaFunction's "matchCriteria" not 100% same as Excel's version

Added by Miro Eklund over 1 year ago. Updated about 1 year ago.

Status:In ProgressStart date:
Priority:4Due date:
Assignee:Miro Eklund% Done:

0%

Category:SpreadsheetSpent time:-
Target version:-
Release notes:
Tags: spreadsheet
Story points-
Velocity based estimate-

Description

The current version of SumifFormulaFunction takes into account most situations, but will handle error-values differently than excel.

Update: Use attached xlsx-file to see the difference in criteria evaluation in excel. If a value includes an error-cell, such as #REF!, Excel will in the example case pass that error forward to SumIf's error. This is not the case in the current SumIf-formula.

When editing SumIf to be the same as Excel's, follow excel specifications for criteria:

[quote]
"*criteria:* Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, ">32", B5, "32", "apples", or TODAY.

Important: Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required."
[/quote]

From: https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b

testing.xlsx - A small example file to show how sumif-formula is evaluated differently (8.43 KB) Miro Eklund, 2016-12-20 09:45 am


Related issues

Related to Platform - Enhancement #6581: FormulaFunctions now recognize excel error values and han... Closed 2016-07-06 2016-07-06

History

#1 Updated by Miro Eklund over 1 year ago

  • Status changed from New to Closed

#2 Updated by Tuukka Lehtonen about 1 year ago

  • Tags set to spreadsheet
  • Category set to Spreadsheet
  • Assignee set to Miro Eklund
  • Release set to 38

Miro, are there any changes related to this issue on the platform side?

#3 Updated by Miro Eklund about 1 year ago

  • Subject changed from SumifFormulaFunction's "matchCriteria" only compares Numbers, rather than expressions, Strings and numbers. to SumifFormulaFunction's "matchCriteria" not 100% same as Excel's version
  • Description updated (diff)

#4 Updated by Tuukka Lehtonen about 1 year ago

  • Due date deleted (2016-07-06)
  • Status changed from Closed to In Progress
  • Target version deleted (1.24.0)
  • Start date deleted (2016-07-06)
  • Release changed from 38 to 43

#5 Updated by Tuukka Lehtonen about 1 year ago

  • Release deleted (43)

#6 Updated by Miro Eklund about 1 year ago

Tuukka Lehtonen wrote:

Miro, are there any changes related to this issue on the platform side?

As far as I know, there are no changes to this issue anywhere. SumIf criteria is still evaluated differently than in excel, but in most cases is handled correctly.

Also available in: Atom PDF