SUMIFS error with multiple conditions

[marcusblevin]

This is:

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

What is the expected behavior?

The range is summed if the conditions are met

What is the current behavior?

If only one condition is provided, the range is summed correctly. If multiple conditions are provided the sum is inaccurate. After the XL spreadsheet is loaded, the formula can be refreshed and is then accurate

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();

// add code that show the issue here...
$spreadsheet->setActiveSheetIndex(0);
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Y')
	  ->setCellValue('B1', 'High')
	  ->setCellValue('C1', 55327)
	  ->setCellValue('A2', 'Y')
	  ->setCellValue('B2', 'High')
	  ->setCellValue('C2', 12345)
	  ->setCellValue('A3', 'N')
	  ->setCellValue('B3', 'High')
	  ->setCellValue('C3', 98786)
	  ->setCellValue('A4', 'Y')
	  ->setCellValue('B4', 'Mid')
	  ->setCellValue('C4', 56466)
	  ->setCellValue('A5', 'Y')
	  ->setCellValue('B5', 'Mid')
	  ->setCellValue('C5', 79843)
	  ->setCellValue('A6', 'N')
	  ->setCellValue('B6', 'Mid')
	  ->setCellValue('C6', 12654)
	  ->setCellValue('A7', 'Y')
	  ->setCellValue('B7', 'Long')
	  ->setCellValue('C7', 65132)
	  ->setCellValue('A8', 'Y')
	  ->setCellValue('B8', 'Long')
	  ->setCellValue('C8', 75312)
	  ->setCellValue('A9', 'N')
	  ->setCellValue('B9', 'Long')
	  ->setCellValue('C9', 96315);

	  
$sheet->setCellValue('E1', 'High')
	  ->setCellValue('F1', '=IFERROR(SUMIFS(C1:C9, A1:A9,"=Y", B1:B9,"=High"),0)')
	  ->setCellValue('G1', '=IFERROR(SUMIFS(C1:C9, B1:B9,"=High"),0)')
	  ->setCellValue('E2', 'Mid')
	  ->setCellValue('F2', '=IFERROR(SUMIFS(C1:C9, A1:A9,"=Y", B1:B9,"=Mid"),0)')
	  ->setCellValue('G2', '=IFERROR(SUMIFS(C1:C9, B1:B9,"=Mid"),0)')
	  ->setCellValue('E3', 'Long')
	  ->setCellValue('F3', '=IFERROR(SUMIFS(C1:C9, A1:A9,"=Y", B1:B9,"=Long"),0)')
	  ->setCellValue('G3', '=IFERROR(SUMIFS(C1:C9, B1:B9,"=Long"),0)');

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet: 1.4.1
PHP: 7.2.10

[PowerKiKi]

Can reproduce the issue with the following simplified code. Expecting 2, but got 5. It seems that a cell is summed multiple times if it match the several conditions whereas we should only sum it once, even if matching several times.

PR are welcome to fix this.

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

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$spreadsheet->setActiveSheetIndex(0);
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Y')
    ->setCellValue('B1', 'High')
    ->setCellValue('C1', 1)
    ->setCellValue('A2', 'Y')
    ->setCellValue('B2', 'High')
    ->setCellValue('C2', 1)
    ->setCellValue('A3', 'N')
    ->setCellValue('B3', 'High')
    ->setCellValue('C3', 1);

$sheet->setCellValue('F1', '=SUMIFS(C1:C9, A1:A9,"=Y", B1:B9,"=High")');

$actual = $sheet->getCell('F1')->getCalculatedValue();

var_dump([$actual, $actual == 2]);

[marcusblevin]

Submitted a PR #710

My first one so apologies if it isn't to standard