package org.apache.poi.ss.formula.eval; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory; import org.junit.jupiter.api.Test; import static org.assertj.core.api.Assertions.assertThat; class Bug6xxxxSumproductCountifBugTest { @Test void formulaEvaluate_should_provide_correct_result() { // given XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook(); FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); String sheetName = "Sheet1"; Sheet sheet = workbook.createSheet(sheetName); Row row1 = sheet.createRow(0); Row row2 = sheet.createRow(1); Row row3 = sheet.createRow(2); Cell cellB1 = row1.createCell(1); Cell cellB2 = row2.createCell(1); Cell cellB3 = row3.createCell(1); Cell cellC1 = row1.createCell(2); Cell cellC2 = row2.createCell(2); Cell cellC3 = row3.createCell(2); Cell cellA1 = row1.createCell(0); String val1 = "a"; String val2 = "a"; String val3 = "b"; String criterion1 = "a"; String criterion2 = "b"; String criterion3 = "a"; cellB1.setCellValue(val1); cellB2.setCellValue(val2); cellB3.setCellValue(val3); cellC1.setCellValue(criterion1); cellC2.setCellValue(criterion2); cellC3.setCellValue(criterion3); cellA1.setCellFormula("SUMPRODUCT(COUNTIF(B1:B3, C1:C3))"); // when CellValue cellValue = formulaEvaluator.evaluate(cellA1); // then assertThat(cellValue.getNumberValue()).isEqualTo(5); } }