sumif() not calculating when comparing numerical values

[DuckDensity]

This is:

- [x] a bug report
- [ ] a feature request
- [x] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Calculation of the resultant value in the saved spreadsheet

What is the current behavior?

The value is calculated correctly if the criteria for sumif() is a string.
The value is zero if the criteria is numeric.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// Some sample data
$spreadsheet->getActiveSheet()->setCellValue('A1', "Text-Test")
                              ->setCellValue('A2', "Text-Test")
                              ->setCellValue('A3', "Text-Testing")
                              ->setCellValue('A4', "Text-Testing")
                              ->setCellValue('B1', "5")
                              ->setCellValue('B2', "8")
                              ->setCellValue('B3', "12")
                              ->setCellValue('B4', "15")
                              ->setCellValue('C1', 0)
                              ->setCellValue('C2', 0.2)
                              ->setCellValue('C3', 0)
                              ->setCellValue('C4', 0.2);

// This sumif() works when comparing text, and gives result of 13 (5+8) (Cells B1 + B2)
$spreadsheet->getActiveSheet()->setCellValue('A5', "=SUMIF(A1:A4,\"Text-Test\",B1:B4)");
$spreadsheet->getActiveSheet()->setCellValue('A6', "A5 Should be 13");

// This sumif() does not calculate and zero is saved in the spread sheet
// Selecting the cell for editing in Excel and then accepting forces Excel
// to calculate the cell value of 17 (5+12) (Cells B1 + B3)
$spreadsheet->getActiveSheet()->setCellValue('C5', "=SUMIF(C1:C4,0,B1:B4)");
$spreadsheet->getActiveSheet()->setCellValue('C6', "C5 Should be 17");

// Write spreadsheet
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$objWriter->save("test.xlsx");

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet [1.4.0] - 2018-08-06
PHP 7.1.19

[PowerKiKi]

I can reproduce with this slightly simplified code:

<?php
require 'vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// Some sample data
$spreadsheet->getActiveSheet()
    ->setCellValue('B1', "5")
    ->setCellValue('B2', "8")
    ->setCellValue('B3', "12")
    ->setCellValue('B4', "15")
    ->setCellValue('C1', 0)
    ->setCellValue('C2', 0.2)
    ->setCellValue('C3', 0)
    ->setCellValue('C4', 0.2);

// This sumif() does not calculate and zero is saved in the spread sheet
// Selecting the cell for editing in Excel and then accepting forces Excel
// to calculate the cell value of 17 (5+12) (Cells B1 + B3)
$spreadsheet->getActiveSheet()->setCellValue('C5', "=SUMIF(C1:C4,0,B1:B4)");
$spreadsheet->getActiveSheet()->setCellValue('C6', "C5 Should be 17");

$actual = $spreadsheet->getActiveSheet()->getCell('C5')->getCalculatedValue();
var_dump([$actual, $actual == 17]);

PR would be very welcome

[DuckDensity]

After submitting the report I did investigate this further, and found that it affected CountIf and AverageIf in the same way. I also discovered a flaw in the AverageIf function which I will report separately.

This morning I have spent a bit more time on it, and found the code that relates to the bug I originally described, which is on line 270 of Functions.php. This lines replaces numerical conditions with a test for an empty string.

My proposal was to add in an additional if test for numeric conditions, Although I then looked at the pull requests, and see that someone else has proposed the same fix a few days ago #701.

[ilicmsreten]

PR #701