Aggregate functions SUMIFS / AVERAGEIFS doesn't apply logical AND between parameters

[Aymeric-Henry]

Description

The aggregate functions usings IFS statement doesn't properly apply the filtering. All parameters must be logical AND between them.

As the specification found in

More precisely :
=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom") |

..."Tom" in Criteria_range2 C2:C9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 20.

Steps to reproduce the issue:

1. In calc_test.go on TestCalcSUMIFSAndAVERAGEIFS function
2. Add test of formula with two first arguments as valid condition and two other arguments as imposible condition

• =AVERAGEIFS(D2:D13,A2:A13,1,A2:A13,2)
• =SUMIFS(D2:D13,A2:A13,1,A2:A13,2)

0. the fork with the tests already set https://github.com/Aymeric-Henry/excelize-fix/tree/1564-sumifs-averageifs-filtering-test
   Describe the results you received:

• =AVERAGEIFS(D2:D13,A2:A13,1,A2:A13,2) -> 268000
• =SUMIFS(D2:D13,A2:A13,1,A2:A13,2) -> 804000
  Describe the results you expected:
• =SUMIFS(D2:D13,A2:A13,1,A2:A13,2) -> Error "AVERAGEIF divide by zero"
• =SUMIFS(D2:D13,A2:A13,1,A2:A13,2) -> 0

Output of go version:

go version go1.20.5 darwin/arm64

Excelize version or commit ID:

commit: f8aa3ad

Environment details (OS, Microsoft Excel™ version, physical, etc.):
Model Name: MacBook Pro
Model Identifier: MacBookPro18,1
Chip: Apple M1 Pro
Total Number of Cores: 10 (8 performance and 2 efficiency)
Memory: 16 GB
System Firmware Version: 7459.101.2
OS Loader Version: 7459.101.2
Serial Number (system): GD413WG4P0
Hardware UUID: 9B37333F-C44D-5460-A79D-CDAEF01E4CE3
Provisioning UDID: 00006000-0018704A3EB8401E
Activation Lock Status: Enabled

[xuri]

Thanks for your feedback. I have fixed this issue, please try to upgrade the master branch code, and this patch will be released in the next version.