From 0d6bd263fefac15d069778fc2041166ac108a6da Mon Sep 17 00:00:00 2001
From: PJ Fanning <fanningpj@apache.org>
Date: Fri, 30 Jul 2021 12:33:15 +0000
Subject: [PATCH] SUMIFS should not sum if a value is #N/A

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1891898 13f79535-47bb-0310-9956-ffa450edef68
---
 .../poi/ss/formula/functions/Baseifs.java       | 17 +++++++++++------
 .../apache/poi/ss/formula/functions/Sumif.java  |  5 ++++-
 2 files changed, 15 insertions(+), 7 deletions(-)

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java
index 88ce9e3f4c..9bee7efb7c 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java
@@ -121,8 +121,10 @@ import org.apache.poi.ss.formula.functions.Countif.ErrorMatcher;
      * @param ranges  criteria ranges
      * @param predicates  array of predicates, a predicate for each value in <code>ranges</code>
      * @return the computed value
+     * @throws EvaluationException if there is an issue with eval
      */
-    private static double aggregateMatchingCells(AreaEval sumRange, AreaEval[] ranges, I_MatchPredicate[] predicates) {
+    private static double aggregateMatchingCells(AreaEval sumRange, AreaEval[] ranges, I_MatchPredicate[] predicates)
+            throws EvaluationException {
         int height = ranges[0].getHeight();
         int width = ranges[0].getWidth();
 
@@ -158,17 +160,20 @@ import org.apache.poi.ss.formula.functions.Countif.ErrorMatcher;
      * @param relRowIndex
      * @param relColIndex
      * @return the aggregate input value corresponding to the given range coordinates
+     * @throws EvaluationException if there is an issue with eval
      */
-    private static double accumulate(AreaEval sumRange, int relRowIndex, int relColIndex) {
+    private static double accumulate(AreaEval sumRange, int relRowIndex, int relColIndex) throws EvaluationException {
         if (sumRange == null) return 1.0; // count
         
         ValueEval addend = sumRange.getRelativeValue(relRowIndex, relColIndex);
         if (addend instanceof NumberEval) {
-            return ((NumberEval)addend).getNumberValue();
+            return ((NumberEval) addend).getNumberValue();
+        } else if (addend instanceof ErrorEval) {
+            throw new EvaluationException((ErrorEval)addend);
+        } else {
+            // everything else (including string and boolean values) counts as zero
+            return 0.0;
         }
-        // everything else (including string and boolean values) counts as zero
-        return 0.0;
-
     }
 
     protected static AreaEval convertRangeArg(ValueEval eval) throws EvaluationException {
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumif.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumif.java
index e9a9f2c9c9..899ff71679 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumif.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumif.java
@@ -102,8 +102,11 @@ public final class Sumif extends Var2or3ArgFunction {
             ValueEval addend = aeSum.getRelativeValue(relRowIndex, relColIndex);
             if (addend instanceof NumberEval) {
                 return ((NumberEval) addend).getNumberValue();
+            } else if (addend instanceof ErrorEval) {
+                throw new EvaluationException((ErrorEval)addend);
             } else {
-                throw new EvaluationException(ErrorEval.NA);
+                // everything else (including string and boolean values) counts as zero
+                return 0.0;
             }
         }
     }
-- 
2.39.5