From 09fc45feb9e3d7816dd19dd6eda0dfda92b61a10 Mon Sep 17 00:00:00 2001
From: Josh Micich <josh@apache.org>
Date: Fri, 11 Jul 2008 07:59:44 +0000
Subject: [PATCH] Patch 45289 - finished support for special comparison
 operators in COUNTIF

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@675853 13f79535-47bb-0310-9956-ffa450edef68
---
 src/documentation/content/xdocs/changes.xml   |   1 +
 src/documentation/content/xdocs/status.xml    |   1 +
 .../record/formula/functions/Countif.java     | 344 ++++++++++++++----
 .../hssf/record/formula/functions/Offset.java |  96 +----
 .../poi/hssf/data/FormulaEvalTestData.xls     | Bin 137216 -> 137728 bytes
 .../apache/poi/hssf/data/countifExamples.xls  | Bin 0 -> 26112 bytes
 .../formula/functions/TestCountFuncs.java     | 159 ++++++++
 7 files changed, 449 insertions(+), 152 deletions(-)
 create mode 100644 src/testcases/org/apache/poi/hssf/data/countifExamples.xls

diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml
index e484ebbf1a..a9cee30b25 100644
--- a/src/documentation/content/xdocs/changes.xml
+++ b/src/documentation/content/xdocs/changes.xml
@@ -37,6 +37,7 @@
 
 		<!-- Don't forget to update status.xml too! -->
         <release version="3.1.1-alpha1" date="2008-??-??">
+           <action dev="POI-DEVELOPERS" type="fix">45289 - finished support for special comparison operators in COUNTIF</action>
            <action dev="POI-DEVELOPERS" type="fix">45126 - Avoid generating multiple NamedRanges with the same name, which Excel dislikes</action>
            <action dev="POI-DEVELOPERS" type="fix">Fix cell.getRichStringCellValue() for formula cells with string results</action>
            <action dev="POI-DEVELOPERS" type="fix">45365 - Handle more excel number formatting rules in FormatTrackingHSSFListener / XLS2CSVmra</action>
diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml
index 767b33521b..6d5d51ea81 100644
--- a/src/documentation/content/xdocs/status.xml
+++ b/src/documentation/content/xdocs/status.xml
@@ -34,6 +34,7 @@
 	<!-- Don't forget to update changes.xml too! -->
     <changes>
         <release version="3.1.1-alpha1" date="2008-??-??">
+           <action dev="POI-DEVELOPERS" type="fix">45289 - finished support for special comparison operators in COUNTIF</action>
            <action dev="POI-DEVELOPERS" type="fix">45126 - Avoid generating multiple NamedRanges with the same name, which Excel dislikes</action>
            <action dev="POI-DEVELOPERS" type="fix">Fix cell.getRichStringCellValue() for formula cells with string results</action>
            <action dev="POI-DEVELOPERS" type="fix">45365 - Handle more excel number formatting rules in FormatTrackingHSSFListener / XLS2CSVmra</action>
diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java b/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java
index 2e445a8bf6..902a991b37 100644
--- a/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java
+++ b/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java
@@ -15,14 +15,17 @@
 * limitations under the License.
 */
 
-
 package org.apache.poi.hssf.record.formula.functions;
 
+import java.util.regex.Pattern;
+
 import org.apache.poi.hssf.record.formula.eval.AreaEval;
+import org.apache.poi.hssf.record.formula.eval.BlankEval;
 import org.apache.poi.hssf.record.formula.eval.BoolEval;
 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
 import org.apache.poi.hssf.record.formula.eval.Eval;
 import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
 import org.apache.poi.hssf.record.formula.eval.RefEval;
 import org.apache.poi.hssf.record.formula.eval.StringEval;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
@@ -40,85 +43,288 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval;
  * @author Josh Micich
  */
 public final class Countif implements Function {
-	
+
+	private static final class CmpOp {
+		public static final int NONE = 0;
+		public static final int EQ = 1;
+		public static final int NE = 2;
+		public static final int LE = 3;
+		public static final int LT = 4;
+		public static final int GT = 5;
+		public static final int GE = 6;
+
+		public static final CmpOp OP_NONE = op("", NONE);
+		public static final CmpOp OP_EQ = op("=", EQ);
+		public static final CmpOp OP_NE = op("<>", NE);
+		public static final CmpOp OP_LE = op("<=", LE);
+		public static final CmpOp OP_LT = op("<", LT);
+		public static final CmpOp OP_GT = op(">", GT);
+		public static final CmpOp OP_GE = op(">=", GE);
+		private final String _representation;
+		private final int _code;
+
+		private static CmpOp op(String rep, int code) {
+			return new CmpOp(rep, code);
+		}
+		private CmpOp(String representation, int code) {
+			_representation = representation;
+			_code = code;
+		}
+		/**
+		 * @return number of characters used to represent this operator
+		 */
+		public int getLength() {
+			return _representation.length();
+		}
+		public int getCode() {
+			return _code;
+		}
+		public static CmpOp getOperator(String value) {
+			int len = value.length();
+			if (len < 1) {
+				return OP_NONE;
+			}
+
+			char firstChar = value.charAt(0);
+
+			switch(firstChar) {
+				case '=':
+					return OP_EQ;
+				case '>':
+					if (len > 1) {
+						switch(value.charAt(1)) {
+							case '=':
+								return OP_GE;
+						}
+					}
+					return OP_GT;
+				case '<':
+					if (len > 1) {
+						switch(value.charAt(1)) {
+							case '=':
+								return OP_LE;
+							case '>':
+								return OP_NE;
+						}
+					}
+					return OP_LT;
+			}
+			return OP_NONE;
+		}
+		public boolean evaluate(boolean cmpResult) {
+			switch (_code) {
+				case NONE:
+				case EQ:
+					return cmpResult;
+				case NE:
+					return !cmpResult;
+			}
+			throw new RuntimeException("Cannot call boolean evaluate on non-equality operator '" 
+					+ _representation + "'");
+		}
+		public boolean evaluate(int cmpResult) {
+			switch (_code) {
+				case NONE:
+				case EQ:
+					return cmpResult == 0;
+				case NE: return cmpResult == 0;
+				case LT: return cmpResult <  0;
+				case LE: return cmpResult <= 0;
+				case GT: return cmpResult >  0;
+				case GE: return cmpResult <= 0;
+			}
+			throw new RuntimeException("Cannot call boolean evaluate on non-equality operator '" 
+					+ _representation + "'");
+		}
+		public String toString() {
+			StringBuffer sb = new StringBuffer(64);
+			sb.append(getClass().getName());
+			sb.append(" [").append(_representation).append("]");
+			return sb.toString();
+		}
+	}
+
 	/**
 	 * Common interface for the matching criteria.
 	 */
-	private interface I_MatchPredicate {
+	/* package */ interface I_MatchPredicate {
 		boolean matches(Eval x);
 	}
-	
+
 	private static final class NumberMatcher implements I_MatchPredicate {
 
 		private final double _value;
+		private final CmpOp _operator;
 
-		public NumberMatcher(double value) {
+		public NumberMatcher(double value, CmpOp operator) {
 			_value = value;
+			_operator = operator;
 		}
 
 		public boolean matches(Eval x) {
+			double testValue;
 			if(x instanceof StringEval) {
 				// if the target(x) is a string, but parses as a number
 				// it may still count as a match
 				StringEval se = (StringEval)x;
-				Double val = parseDouble(se.getStringValue());
+				Double val = OperandResolver.parseDouble(se.getStringValue());
 				if(val == null) {
 					// x is text that is not a number
 					return false;
 				}
-				return val.doubleValue() == _value;
-			}
-			if(!(x instanceof NumberEval)) {
+				testValue = val.doubleValue();
+			} else if((x instanceof NumberEval)) {
+				NumberEval ne = (NumberEval) x;
+				testValue = ne.getNumberValue();
+			} else {
 				return false;
 			}
-			NumberEval ne = (NumberEval) x;
-			return ne.getNumberValue() == _value;
+			return _operator.evaluate(Double.compare(testValue, _value));
 		}
 	}
 	private static final class BooleanMatcher implements I_MatchPredicate {
 
-		private final boolean _value;
+		private final int _value;
+		private final CmpOp _operator;
 
-		public BooleanMatcher(boolean value) {
-			_value = value;
+		public BooleanMatcher(boolean value, CmpOp operator) {
+			_value = boolToInt(value);
+			_operator = operator;
+		}
+
+		private static int boolToInt(boolean value) {
+			return value ? 1 : 0;
 		}
 
 		public boolean matches(Eval x) {
+			int testValue;
 			if(x instanceof StringEval) {
+				if (true) { // change to false to observe more intuitive behaviour
+					// Note - Unlike with numbers, it seems that COUNTIF never matches 
+					// boolean values when the target(x) is a string
+					return false;
+				}
 				StringEval se = (StringEval)x;
 				Boolean val = parseBoolean(se.getStringValue());
 				if(val == null) {
 					// x is text that is not a boolean
 					return false;
 				}
-				if (true) { // change to false to observe more intuitive behaviour
-					// Note - Unlike with numbers, it seems that COUNTA never matches 
-					// boolean values when the target(x) is a string
-					return false;
-				}
-				return val.booleanValue() == _value;
-			}
-			if(!(x instanceof BoolEval)) {
+				testValue = boolToInt(val.booleanValue());
+			} else if((x instanceof BoolEval)) {
+				BoolEval be = (BoolEval) x;
+				testValue = boolToInt(be.getBooleanValue());
+			} else {
 				return false;
 			}
-			BoolEval be = (BoolEval) x;
-			return be.getBooleanValue() == _value;
+			return _operator.evaluate(testValue - _value);
 		}
 	}
 	private static final class StringMatcher implements I_MatchPredicate {
 
 		private final String _value;
+		private final CmpOp _operator;
+		private final Pattern _pattern;
 
-		public StringMatcher(String value) {
+		public StringMatcher(String value, CmpOp operator) {
 			_value = value;
+			_operator = operator;
+			switch(operator.getCode()) {
+				case CmpOp.NONE:
+				case CmpOp.EQ:
+				case CmpOp.NE:
+					_pattern = getWildCardPattern(value);
+					break;
+				default:
+					_pattern = null;
+			}
 		}
 
 		public boolean matches(Eval x) {
+			if (x instanceof BlankEval) {
+				switch(_operator.getCode()) {
+					case CmpOp.NONE:
+					case CmpOp.EQ:
+						return _value.length() == 0;
+				}
+				// no other criteria matches a blank cell
+				return false;
+			}
 			if(!(x instanceof StringEval)) {
+				// must always be string
+				// even if match str is wild, but contains only digits
+				// e.g. '4*7', NumberEval(4567) does not match
 				return false;
 			}
-			StringEval se = (StringEval) x;
-			return se.getStringValue() == _value;
+			String testedValue = ((StringEval) x).getStringValue();
+			if (testedValue.length() < 1 && _value.length() < 1) {
+				// odd case: criteria '=' behaves differently to criteria ''
+
+				switch(_operator.getCode()) {
+					case CmpOp.NONE: return true;
+					case CmpOp.EQ:   return false;
+					case CmpOp.NE:   return true;
+				}
+				return false;
+			}
+			if (_pattern != null) {
+				return _operator.evaluate(_pattern.matcher(testedValue).matches());
+			}
+			return _operator.evaluate(testedValue.compareTo(_value));
+		}
+		/**
+		 * Translates Excel countif wildcard strings into java regex strings
+		 * @return <code>null</code> if the specified value contains no special wildcard characters.
+		 */
+		private static Pattern getWildCardPattern(String value) {
+			int len = value.length();
+			StringBuffer sb = new StringBuffer(len);
+			boolean hasWildCard = false;
+			for(int i=0; i<len; i++) {
+				char ch = value.charAt(i);
+				switch(ch) {
+					case '?':
+						hasWildCard = true;
+						// match exactly one character
+						sb.append('.');
+						continue;
+					case '*':
+						hasWildCard = true;
+						// match one or more occurrences of any character
+						sb.append(".*");
+						continue;
+					case '~':
+						if (i+1<len) {
+							ch = value.charAt(i+1);
+							switch (ch) {
+								case '?':
+								case '*':
+									hasWildCard = true;
+									sb.append('[').append(ch).append(']');
+									i++; // Note - incrementing loop variable here
+									continue;
+							}
+						}
+						// else not '~?' or '~*'
+						sb.append('~'); // just plain '~'
+						continue;
+					case '.':
+					case '$':
+					case '^':
+					case '[':
+					case ']':
+					case '(':
+					case ')':
+						// escape literal characters that would have special meaning in regex 
+						sb.append("\\").append(ch);
+						continue;
+				}
+				sb.append(ch);
+			}
+			if (hasWildCard) {
+				return Pattern.compile(sb.toString());
+			}
+			return null;
 		}
 	}
 
@@ -132,8 +338,7 @@ public final class Countif implements Function {
 				// perhaps this should be an exception
 				return ErrorEval.VALUE_INVALID;
 		}
-		
-		AreaEval range = (AreaEval) args[0];
+
 		Eval criteriaArg = args[1];
 		if(criteriaArg instanceof RefEval) {
 			// criteria is not a literal value, but a cell reference
@@ -144,31 +349,47 @@ public final class Countif implements Function {
 			// other non literal tokens such as function calls, have been fully evaluated
 			// for example COUNTIF(B2:D4, COLUMN(E1))
 		}
+		if(criteriaArg instanceof BlankEval) {
+			// If the criteria arg is a reference to a blank cell, countif always returns zero.
+			return NumberEval.ZERO;
+		}
 		I_MatchPredicate mp = createCriteriaPredicate(criteriaArg);
-		return countMatchingCellsInArea(range, mp);
+		return countMatchingCellsInArea(args[0], mp);
 	}
 	/**
 	 * @return the number of evaluated cells in the range that match the specified criteria
 	 */
-	private Eval countMatchingCellsInArea(AreaEval range, I_MatchPredicate criteriaPredicate) {
-		ValueEval[] values = range.getValues();
+	private Eval countMatchingCellsInArea(Eval rangeArg, I_MatchPredicate criteriaPredicate) {
 		int result = 0;
-		for (int i = 0; i < values.length; i++) {
-			if(criteriaPredicate.matches(values[i])) {
+		if (rangeArg instanceof RefEval) {
+			RefEval refEval = (RefEval) rangeArg;
+			if(criteriaPredicate.matches(refEval.getInnerValueEval())) {
 				result++;
 			}
+		} else if (rangeArg instanceof AreaEval) {
+
+			AreaEval range = (AreaEval) rangeArg;
+			ValueEval[] values = range.getValues();
+			for (int i = 0; i < values.length; i++) {
+				if(criteriaPredicate.matches(values[i])) {
+					result++;
+				}
+			}
+		} else {
+			throw new IllegalArgumentException("Bad range arg type (" + rangeArg.getClass().getName() + ")");
 		}
 		return new NumberEval(result);
 	}
-	
-	private static I_MatchPredicate createCriteriaPredicate(Eval evaluatedCriteriaArg) {
+
+	/* package */ static I_MatchPredicate createCriteriaPredicate(Eval evaluatedCriteriaArg) {
+
 		if(evaluatedCriteriaArg instanceof NumberEval) {
-			return new NumberMatcher(((NumberEval)evaluatedCriteriaArg).getNumberValue());
+			return new NumberMatcher(((NumberEval)evaluatedCriteriaArg).getNumberValue(), CmpOp.OP_NONE);
 		}
 		if(evaluatedCriteriaArg instanceof BoolEval) {
-			return new BooleanMatcher(((BoolEval)evaluatedCriteriaArg).getBooleanValue());
+			return new BooleanMatcher(((BoolEval)evaluatedCriteriaArg).getBooleanValue(), CmpOp.OP_NONE);
 		}
-		
+
 		if(evaluatedCriteriaArg instanceof StringEval) {
 			return createGeneralMatchPredicate((StringEval)evaluatedCriteriaArg);
 		}
@@ -181,50 +402,29 @@ public final class Countif implements Function {
 	 */
 	private static I_MatchPredicate createGeneralMatchPredicate(StringEval stringEval) {
 		String value = stringEval.getStringValue();
-		char firstChar = value.charAt(0);
+		CmpOp operator = CmpOp.getOperator(value);
+		value = value.substring(operator.getLength());
+
 		Boolean booleanVal = parseBoolean(value);
 		if(booleanVal != null) {
-			return new BooleanMatcher(booleanVal.booleanValue());
+			return new BooleanMatcher(booleanVal.booleanValue(), operator);
 		}
-		
-		Double doubleVal = parseDouble(value);
+
+		Double doubleVal = OperandResolver.parseDouble(value);
 		if(doubleVal != null) {
-			return new NumberMatcher(doubleVal.doubleValue());
-		}
-		switch(firstChar) {
-			case '>':
-			case '<':
-			case '=':
-				throw new RuntimeException("Incomplete code - criteria expressions such as '"
-						+ value + "' not supported yet");
+			return new NumberMatcher(doubleVal.doubleValue(), operator);
 		}
-		
-		//else - just a plain string with no interpretation.
-		return new StringMatcher(value);
-	}
 
-	/**
-	 * Under certain circumstances COUNTA will equate a plain number with a string representation of that number
-	 */
-	/* package */ static Double parseDouble(String strRep) {
-		if(!Character.isDigit(strRep.charAt(0))) {
-			// avoid using NumberFormatException to tell when string is not a number
-			return null;
-		}
-		// TODO - support notation like '1E3' (==1000)
-		
-		double val;
-		try {
-			val = Double.parseDouble(strRep);
-		} catch (NumberFormatException e) {
-			return null;
-		}
-		return new Double(val);
+		//else - just a plain string with no interpretation.
+		return new StringMatcher(value, operator);
 	}
 	/**
 	 * Boolean literals ('TRUE', 'FALSE') treated similarly but NOT same as numbers. 
 	 */
 	/* package */ static Boolean parseBoolean(String strRep) {
+		if (strRep.length() < 1) {
+			return null;
+		}
 		switch(strRep.charAt(0)) {
 			case 't':
 			case 'T':
diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Offset.java b/src/java/org/apache/poi/hssf/record/formula/functions/Offset.java
index 9497a5f21a..f61434d740 100644
--- a/src/java/org/apache/poi/hssf/record/formula/functions/Offset.java
+++ b/src/java/org/apache/poi/hssf/record/formula/functions/Offset.java
@@ -25,7 +25,9 @@ import org.apache.poi.hssf.record.formula.eval.AreaEval;
 import org.apache.poi.hssf.record.formula.eval.BoolEval;
 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
 import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
 import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
 import org.apache.poi.hssf.record.formula.eval.Ref3DEval;
 import org.apache.poi.hssf.record.formula.eval.RefEval;
 import org.apache.poi.hssf.record.formula.eval.StringEval;
@@ -55,21 +57,6 @@ public final class Offset implements FreeRefFunction {
 	private static final int LAST_VALID_COLUMN_INDEX = 0xFF;
 	
 
-	/**
-	 * Exceptions are used within this class to help simplify flow control when error conditions
-	 * are encountered 
-	 */
-	private static final class EvalEx extends Exception {
-		private final ErrorEval _error;
-
-		public EvalEx(ErrorEval error) {
-			_error = error;
-		}
-		public ErrorEval getError() {
-			return _error;
-		}
-	}
-	
 	/** 
 	 * A one dimensional base + offset.  Represents either a row range or a column range.
 	 * Two instances of this class together specify an area range.
@@ -133,8 +120,7 @@ public final class Offset implements FreeRefFunction {
 			return sb.toString();
 		}
 	}
-	
-	
+
 	/**
 	 * Encapsulates either an area or cell reference which may be 2d or 3d.
 	 */
@@ -175,19 +161,15 @@ public final class Offset implements FreeRefFunction {
 		public int getWidth() {
 			return _width;
 		}
-
 		public int getHeight() {
 			return _height;
 		}
-
 		public int getFirstRowIndex() {
 			return _firstRowIndex;
 		}
-
 		public int getFirstColumnIndex() {
 			return _firstColumnIndex;
 		}
-
 		public boolean isIs3d() {
 			return _externalSheetIndex > 0;
 		}
@@ -198,7 +180,6 @@ public final class Offset implements FreeRefFunction {
 			}
 			return (short) _externalSheetIndex;
 		}
-
 	}
 	
 	public ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, HSSFWorkbook workbook, HSSFSheet sheet) {
@@ -207,7 +188,6 @@ public final class Offset implements FreeRefFunction {
 			return ErrorEval.VALUE_INVALID;
 		}
 		
-		
 		try {
 			BaseRef baseRef = evaluateBaseRef(args[0]);
 			int rowOffset = evaluateIntArg(args[1], srcCellRow, srcCellCol);
@@ -227,24 +207,23 @@ public final class Offset implements FreeRefFunction {
 			LinearOffsetRange rowOffsetRange = new LinearOffsetRange(rowOffset, height);
 			LinearOffsetRange colOffsetRange = new LinearOffsetRange(columnOffset, width);
 			return createOffset(baseRef, rowOffsetRange, colOffsetRange, workbook, sheet);
-		} catch (EvalEx e) {
-			return e.getError();
+		} catch (EvaluationException e) {
+			return e.getErrorEval();
 		}
 	}
 
-
 	private static AreaEval createOffset(BaseRef baseRef, 
 			LinearOffsetRange rowOffsetRange, LinearOffsetRange colOffsetRange, 
-			HSSFWorkbook workbook, HSSFSheet sheet) throws EvalEx {
+			HSSFWorkbook workbook, HSSFSheet sheet) throws EvaluationException {
 
 		LinearOffsetRange rows = rowOffsetRange.normaliseAndTranslate(baseRef.getFirstRowIndex());
 		LinearOffsetRange cols = colOffsetRange.normaliseAndTranslate(baseRef.getFirstColumnIndex());
 		
 		if(rows.isOutOfBounds(0, LAST_VALID_ROW_INDEX)) {
-			throw new EvalEx(ErrorEval.REF_INVALID);
+			throw new EvaluationException(ErrorEval.REF_INVALID);
 		}
 		if(cols.isOutOfBounds(0, LAST_VALID_COLUMN_INDEX)) {
-			throw new EvalEx(ErrorEval.REF_INVALID);
+			throw new EvaluationException(ErrorEval.REF_INVALID);
 		}
 		if(baseRef.isIs3d()) {
 			Area3DPtg a3dp = new Area3DPtg(rows.getFirstIndex(), rows.getLastIndex(), 
@@ -260,8 +239,7 @@ public final class Offset implements FreeRefFunction {
 		return HSSFFormulaEvaluator.evaluateAreaPtg(sheet, workbook, ap);
 	}
 
-
-	private static BaseRef evaluateBaseRef(Eval eval) throws EvalEx {
+	private static BaseRef evaluateBaseRef(Eval eval) throws EvaluationException {
 		
 		if(eval instanceof RefEval) {
 			return new BaseRef((RefEval)eval);
@@ -270,16 +248,15 @@ public final class Offset implements FreeRefFunction {
 			return new BaseRef((AreaEval)eval);
 		}
 		if (eval instanceof ErrorEval) {
-			throw new EvalEx((ErrorEval) eval);
+			throw new EvaluationException((ErrorEval) eval);
 		}
-		throw new EvalEx(ErrorEval.VALUE_INVALID);
+		throw new EvaluationException(ErrorEval.VALUE_INVALID);
 	}
 
-
 	/**
 	 * OFFSET's numeric arguments (2..5) have similar processing rules
 	 */
-	private static int evaluateIntArg(Eval eval, int srcCellRow, short srcCellCol) throws EvalEx {
+	private static int evaluateIntArg(Eval eval, int srcCellRow, short srcCellCol) throws EvaluationException {
 
 		double d = evaluateDoubleArg(eval, srcCellRow, srcCellCol);
 		return convertDoubleToInt(d);
@@ -295,18 +272,17 @@ public final class Offset implements FreeRefFunction {
 		return (int)Math.floor(d);
 	}
 	
-	
-	private static double evaluateDoubleArg(Eval eval, int srcCellRow, short srcCellCol) throws EvalEx {
-		ValueEval ve = evaluateSingleValue(eval, srcCellRow, srcCellCol);
+	private static double evaluateDoubleArg(Eval eval, int srcCellRow, short srcCellCol) throws EvaluationException {
+		ValueEval ve = OperandResolver.getSingleValue(eval, srcCellRow, srcCellCol);
 		
 		if (ve instanceof NumericValueEval) {
 			return ((NumericValueEval) ve).getNumberValue();
 		}
 		if (ve instanceof StringEval) {
 			StringEval se = (StringEval) ve;
-			Double d = parseDouble(se.getStringValue());
+			Double d = OperandResolver.parseDouble(se.getStringValue());
 			if(d == null) {
-				throw new EvalEx(ErrorEval.VALUE_INVALID);
+				throw new EvaluationException(ErrorEval.VALUE_INVALID);
 			}
 			return d.doubleValue();
 		}
@@ -319,44 +295,4 @@ public final class Offset implements FreeRefFunction {
 		}
 		throw new RuntimeException("Unexpected eval type (" + ve.getClass().getName() + ")");
 	}
-	
-	private static Double parseDouble(String s) {
-		// TODO - find a home for this method
-		// TODO - support various number formats: sign char, dollars, commas
-		// OFFSET and COUNTIF seem to handle these
-		return Countif.parseDouble(s);
-	}
-	
-	private static ValueEval evaluateSingleValue(Eval eval, int srcCellRow, short srcCellCol) throws EvalEx {
-		if(eval instanceof RefEval) {
-			return ((RefEval)eval).getInnerValueEval();
-		}
-		if(eval instanceof AreaEval) {
-			return chooseSingleElementFromArea((AreaEval)eval, srcCellRow, srcCellCol);
-		}
-		if (eval instanceof ValueEval) {
-			return (ValueEval) eval;
-		}
-		throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
-	}
-
-	// TODO - this code seems to get repeated a bit
-	private static ValueEval chooseSingleElementFromArea(AreaEval ae, int srcCellRow, short srcCellCol) throws EvalEx {
-		if (ae.isColumn()) {
-			if (ae.isRow()) {
-				return ae.getValues()[0];
-			}
-			if (!ae.containsRow(srcCellRow)) {
-				throw new EvalEx(ErrorEval.VALUE_INVALID);
-			}
-			return ae.getValueAt(srcCellRow, ae.getFirstColumn());
-		}
-		if (!ae.isRow()) {
-			throw new EvalEx(ErrorEval.VALUE_INVALID);
-		}
-		if (!ae.containsColumn(srcCellCol)) {
-			throw new EvalEx(ErrorEval.VALUE_INVALID);
-		}
-		return ae.getValueAt(ae.getFirstRow(), srcCellCol);
-	}
 }
diff --git a/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls b/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
index 7be92c5fa43389d199e7ad4e75b75e1c7a2043f7..eba6607ade6da210856ebd818f93e1676e9ce275 100644
GIT binary patch
delta 2086
zcmZWq3rtg27(V|we_MK?+HD{uItRfh2v(>M5G&T_GBCymTeRxL`JCAjoowRfqv+;i
zGO1kk;xwC&;fUJ|rod&JF3r?TKo=hfECYPZDP5dVmN>Gh-G3;EW+bHF`S17m&$<6;
zJ>_AZiZHJe!j6ym2_W2O;7hsLe>1u=>9@0BR^P=<e6`W{4_*}Tof@my6f^SzV~n%V
zGO}lBHOCRhYL=W6yo+Ns_feOd!$V?qhuPdoiB!KfTe{HCUC5@S_sC>YW{-8OBCTc7
z*;Aw>b*<8E_^Bcd%bpOGCq&;J<*J?6UsunMis`qcU3PC_GxHYYt(x-Y8##+t<*oXN
zMX;5O`*_B*%*^9?z)r3Pwr4#s<wIb7wg9Wx1<bDjt3L?r!BJq*mB4160k-!Xu!76<
z>=$6Wnt_$K`n>sGvq&y=_~JKaq8LtK(dImX{@rf$HE*+naF#k$%l3G@_Hd&?9lrev
z*3%=^Qka5l)?KcSD?BD95~aQHFxt!o26gWzNARBHPS~*-%ZnTa_56;JqHVWZH5Tng
zKNWnR7sUx&VIuPB;(^#t+T1N3jp-5$Q6~uN_$c2h^-=tQDWwiWFvwU2Q-Ve|(a6yM
zyphGSsdND2=v?y2!?#P%NJWoyyVPbEk$c>#2n@&E4!1!a9T+PT(66owII-k1F-`_X
zU=GFj9t3J_SVGNcr#U2vb43oVokOy<iApkY9~gsXHnF!B<Pf76JJW7T`g8$F$R2Gp
zXdVI6@Sd#I3rOakD2*eeV1y^isE<S_HbPLZQJ`Z0UQ6*Cv@=8D61oWK`%=IgBXUX|
znmr2=(JIa6WWh*G*yBcRcNV1KR@#nsAq$3~S0Yn3q+*Fg&TPn0=5`%Rn$S{rLbQH7
z8?IPH{UejMZ8q5~du&4O)NB}m2c+cTY)HT_3Zjf!f}7&XC3@8jgE{)O0yptJbRgRI
zZdixAq|!MDQbpNglfGgO*ut<(dK4}MJNl*ahlMayETZ1;ErcQBtx|^`y9k_-8;mTK
z-4=qmSf79E?$(^*E}*p`*q6o71P?GWqd&&Qpg(7wAB>F`Q&~uaBntgmJ4NDuA{G*f
zE>W6vCeh-fxj8ew3@6d44WI<$o{fqY%wbQJX@pYkqe6jWNGo6G@)LQg4eyPWUE<?G
z-BLqmmy@VnnzgWJ7t-JnQ&^ke6#fluOYmN_lAF%x-t~Ge385`nT_M8Ly@o1Jcc3rN
zhd5z5X<{rHj=&PeVTNAv0St~5d34TaiXc&mr5TZ9xdpWaSrDaJywF!f22EO$7t*m@
zzM`dGnwDybKJ(JF_$9jRg&fg#$e|D33BBU6oUVZ8tbkGI3fpAR)>O~{r^x9$Ujgr-
zOQM%6AydqITWDJ=VF~V+lhIxYQ*oMnII#*QW3{a9sDdeYPR>JX6-*H2haB1fo#vLA
z$#=dEuVF_xWqqqd2G&c#-#Sd^#LB9sE5afQ4Ep+N82p@wJ?zlFs(}{;BT!cZFNqFf
z{Ye0uq`kEk62&ztIcw=z6M@$WAZsA^f?DzlBQLG8mPR~Rws^f3l0_NOtaT7CJOoBc
zkWXhYw+=Qa!bUl*SI8?NXxh^NcHyE^OeKJ9Hubr%0UVe+jPC2+1{fh^%*6&s5mu66
zBMcHbM7KAR?gDwwrwUqS2Ol?52P<R;RgKiaGGhHsEbJ#uqnqePoh5#D6HF3*>P2k=
z8;T1?zjmyd&eS7?9nJLGyb=v*fuU%Vwz(}Z5Mw(7S}5SAJd&ywy1NBY+70?dW};TD
zt#sGZWzs9Hl;olCb*;3I99i<ULV>8J%(SZzZlW(r5=_1YOH2|SDBh&|Zb1_8t3tbW
z8-_*%=yos$EFc0x>)QrC)7=}utoK0+1|Y^_qN#rz3<l-DqR0IKzXIQXU-jni*YW~T
zSak<-dmGpO1uQezU7W8kcnFHcxCQ7(*r2a8^Zr~cqthr3=kxf+DD8eYPs9Uqll>$3
zJ7N}<)Ciu0WwP{R1kc5Ci3VEuL89w57M{k{l=mB|tlTBcY%A~l@fYgxHEFu%1@6KB
E07a#Te*gdg

delta 1815
zcmYLKe@v8h82>)+_qpTVmpcSI<tR!BXZ#WlPXQ6>NGylwurLu~VH%NhiGofyU8g09
zqTcZ$Z>3u_hZ2^<PJLl%qR=wIB4^`R@x!eW9H!|im*Vw&k@Sz-KJW8>p3mp`Jnyre
z>x9YGY;vbV!11}?0w`XC^p+&EkiMSR`!0Y<-GJ{&iwxd>@kb7^YJ_4_Wab9On8Y4F
zS|2|K%aOzhmIdduG|mZ@C#$RFz=zH1URfTaSk=#E%Q*Ue9NCoA$8EwX<FRyvBBVtz
z{c(|;)Gf;Yf*&hFu<;R5c|`P%C<6&n)1Z23c361&pJI9od!=GUMS0HKZ{)pMUQxb-
znb}Kz@Jea985tjx15-BwTeB6|;C5gmJAmy{fz{OkYpMs<^%<}mUSKgDz}9vGTXvb=
z`GBqd8CYeX*S*OtbMq00H)?wZ^81G!-c^+q982vx4c=d>5`g0ohicg!g%OiNjq1$Z
zKcS0Wsg`Ox9!xAWsF~HrI3vpK>O*)zA<9R44x?Rg=k2XTbGyr=UfP??8;4}oxNkpp
zS*%9yC;K8fb_;Sx%`|KgGJn*}!Eh%DPgm#h=0*3s<?3kE)Y1lr!S81*jVXR3%Q7--
zn`mSaY%wh$llC%58s1txEeVH&+U21HUQ0%NcqTTlGa1!6!_RZq)gpE4a5@%mpu(Bq
zS=f?E(f%I3XhR!Kr*%jW$MY*4+HDEqgUvLba6AA;zs#~GXnqO86yuoNuiR?XmT-te
zC*{*z9CFb|eC-N{c*$bbBm_I!&bdr_GD3|J9_ZgGaCa&?Gl=v}B3#D$8KFk~fF0an
zyraRPC1gVk<_fXGY)Hn&<edg>e>SAxEsCRcXM+Rp3KXyql8^-wN?!<hysN>Xs|(?d
zg_n-VTGeuDjh@uNwD#pN3r&J{e>p_sRGLJKE+7lLKuZfCR{E_-+g3pQ)ECI=s{+_c
zS*SC;5R!QGkgS&#g3W}%LZf;mB%o7te!UVB`TI={?bb?|&RtCoJz^E4TZ2ZK!iKoS
zoa_s_QoVLFL~@I2WGo&ILkSCKLHhYE5NqXIsQ0Fu1}PR<!#$A9okOy=)B~w#6J8z<
zMU4>Xf`_6;3pDDXsGG<vuLgpm&_&-*D>(_Xv5l6f9Xd(dFB5YAoP>?&5@_8i$l#5%
z>n~413Fc){#Bd!Jqg%XuM~4@Y&8J$64mp@3f|*)i9<QL`7PrvS>*rEyRSUdov6H{J
zf>1+<|4Hq5D_HAV;c3iEu^F|<R#;+gdU%(ih9~@C;{9Z;wiSXgmHPC?R)`IWS~E_0
z-hgzvp^Rm2XGZo6qi5O|XQkx&*F~7ZiwT5w!W3lZX`-}Fh~ckMsf0>=6@i`N-G?Il
z(M~!qr&!HECw<;}kt(nY;(0#N(z+mu=Mg9tAdA*k*#++^+({)<4{7Dm!ee_NftL~}
zB!DgvXHO5zKqiv6_rNSJeD3vto##+rf)AeKE~4lAs4tf)RaC(RVuTYu8lhQ?FzBNZ
z+K4rwmqZT=(ac^trv=2{&<pc<It_EC7i`EIuNG;(KKc%AqVd^&`qbS5E$fFw%oVb`
z`e7R8j~Vonfm!Tiu%8asY}L#I5R0yJq@OlGhu$Kb$_L2l3Yi}npd433sc!(b@l^6$
zF$jSH*xgRGvTINhAdvHJvfg(M;y`KvO&Nwn^I4#SWx&qDS<vPWgIDRe3heirz}DZ=
z-1p(0HaY^+>#l<><bmJs7eAS<eGKbK*Wm~7l$rF{8&EvK`0{OF)(1Lu8G-_e=ugLu
zlIq#CRu2r6jscIRjhr@1MN*Jed&(lkpicx{V3F2yCzZ}vq&OTDr4fr%j6(wD21%b1
fUB43~rAVhHN=`1j@aWjT9aoQUPSIVHBp3b%59}kB

diff --git a/src/testcases/org/apache/poi/hssf/data/countifExamples.xls b/src/testcases/org/apache/poi/hssf/data/countifExamples.xls
new file mode 100644
index 0000000000000000000000000000000000000000..b15bd162a23056dd0d69e87184b8c1fdbed2dce0
GIT binary patch
literal 26112
zcmeHPdvILUc|Uu#l2(4L<oDaxvSit^ElVrgnAp<lX=BjCvE^7a&KNybwrW{&<TZ~J
zgGk9gg)+o0kbqlCQ)Wt&k_O0wDQQF|leihvrfn!g3LTO{N}<yNO{Wb3Z-2jY_TIhc
z?A`0#kYs@I-8sAWe)oL8?>pc5&UYU7?ES&tRlM}jSC;-*7)P@x5*JftqQpZF;haex
z<wDfqgx42SsgxoLfz!*!2gm~NLf3j!UJ+6lsThfMUWyb!DnpuqG!tnS(rlzTNKquX
zfT%#4i!={uKGFiDg-DB#79%Y|T8gv`X*tpgq?JgkkZh#YNR>!ykgAZXk=7#BAgx1M
zk5r4a0cj)BCM5bBQ-kOH17z~|m8$#11U^aBMW-0Y@1(d1R?Yud2#;|JWP#0&@LPy!
z`7L(w=Q~SY`P46daz14LaeHdRzn%|m25m}Z69*xEQXCV5QjcSbtAOvh!V1xE9Ubyv
zrRaMOs+`4Qzc?k1<1--3z6Vwqkvfr*6r(sF4**>U`HX>VKhz;0t9I*r`l?uTK*EsJ
zAt7s^FnzW|zEk#m<<nk2OUo~dpcR)Ci$_*HQ~EcE<|VlKV_}KE3_p!;A%22%NSt7t
zw||pIo77<-L>;Cfya{I^aSqgS#1v9d^Z1@8zrQT=Jte=JaOXPFCzfB^zjixvu#f7u
zT4cW8m*180dk(%s;_H&<N0QQj$n*m8N5tK%QcF~phT7o;WAK7Oc*m%CzvU>qq^9#P
zks~Cwh^J`CoTUw+4+zd-A=+QcVikcoHlht}50nzVWR4e5o52{Y+vY$$m}AQ&bL_lC
zj*y54mgT5VS&pYYY#{ApA$Vc1)!8rSvii#Ka>Qly!If2&Yc{S~Q-65<jzjC5i$m*I
zhF7DPM}O>)aR1}`62qU+GgV?3fDYTqR9CO#;F?f1P_@#`WuT?AUV*+mCd6T?QfoV!
zr7sVylY~R-ccclGn<}B#;dL#CA*BvO0SpEXuk-TN+FqtwTjPjh!0-`Wm@0uTPpak-
z8INa#Zo{|(qYPK5!|%#<n5F0p=Sy-dB*g!uY<QG&OxvV$geB=t2o{&=%0j=K9mpFJ
zwu0cdW#BxKz7>8@x4%pOiyZVn&q04Q2mOH@^e^Y2e<=t34|CA($U)DgPcHn&B@RdV
z(hprb{80|N(kDB=(kGk#r#W!Gl7s$44*Fl`p#NSD`ja{6h3fNFiBp<oALS>U6-m4O
zK}E%VGw+)z!uQJjoGB^#$jG-=eQT8ne_hgx5kn;X|Byi*j(@JtLEo5zUh7Ne>`dYt
z@q{xrNq6g4`7>v6lKw-n7pItrDSbGz^U%Sr=$!4P={8O=@iXX#T+R$-e%C(A|2bPs
z<6MVRObQhaXONQa>iNaA-O}`P6%`9-E`*)$mvqiH9eibHW&dJ~QzYH6GiRofZs<AR
zq5lv=JkjjhfiqaY{2)Vu%^!87;|y_R{Gns;NYi<0(r=E;7dJckm_f9P*Z%(elcyt_
z#A%JrdlqKf^X1Hf!%=p&=%#Atpa-$C8JMLY1~up_Kmw;Y1xesMqaX%$jRi3<_7ucW
zS||f6r636m0|ha#{T9T)hFuT?TYNzb3<U)-Fh&%_z#tLG&}y^?MvtQ0?IA21N@9dA
zr$&sneL7--Fx2D$LO09}!uXR12wgKb2!m1{AavK<AdEbDfY60=gD~9X0YbOV4Z;<_
zOM+bJ3a%Y48Rh{JTsw>N0|~AjE?VZHNpS6O)iV!};M(D`X&xZKwS#${2Lu;-U!vMh
ztTDKDxNw`%BqvC4?QrEc50K#6;nHy)Ai=d`=LZs8JFD{p39cPXoiAM+wK$)<0do2L
zk~UGR`?*1?@&gI3o$CBRf@^1OejvfMQ<EP^aP6#fK{#GICrVP<IE-zFH&WmG+f>G|
zpEWwK^~g~wJ1^#a`?T-}dF!pWT#(W<@U6GrnzGYJ#D`Yqp#fVSGkj>LBc@V(s-l$Q
zz>bG+$sT_A;Vh-%@pzU}UYes6X$qQ)RmxIcTJ|V*1F{E>3(Rpcp9vMjzz0YLG1L{x
zz{eX(0&ANUs2MhUSW=H{s_~Kmvk{%a@v-mzm)FP4u?-)FWg-BpRFg{IAu%|}V{Puz
zAgOpTNMk-AW_M%O>lT1yswt~D+3vg*44F0-k6-U^#MFh)XSwsQu8o<MvduMxZ^L&Q
zbZRQ(DwXXXS7;#T5;@!>_4+vvh}qh=yXK)Sv*weIkEK!<To5M&J@(jRS*_hmb6PuT
zz?O&8+P$>wI=d2iy>%A%uQR{6=nXfH8ND<|DQ~!Ol=9NDLlBmXJ)W0ksceQF8iVPo
zq0^BAMt86i4B^~!8QR@!s@~@7bO+g*G`X74-B202J+m2_T?SvT$YzKa%Fq(baMqfO
z2YQ%q#_Fr>O?K<hP+~mUa3<0%&aiV&AEEHd)NDLBsK@pI8(RQuESn7BqaK*5$QQ!)
zgwk+g<ey)8GgZuU)*LKQ&E*n`sW8RFvc*&eh*7aoiYZQvy!GM>{$l2uVj8o>tPK#O
zqNfy7k{EgI%@_T}%rnJo$re)^Acpa^2yp<0Dou>M^3s3!i<xhV*_th;E<g;Ua#~CT
zb@QM9?Js75DP~)?m{@=q6=!8>$`T{*{`>R(Vir=&mS9g?1u<Ll@U#nE-?;FyznDd)
z7#~kt6ClRxX&1VF`s6qK#Vj_(_;^}PfEcf*UFdr5<){3`EHTCSc-n>lF<wu*&~;(@
zNB&}#nqquBZF7Jaucuw;`r3=H`iog+it+KZ#sD#1PrK0d&a+SWi&;)F+k!pKhL~-6
zcv`ISp2we4Vi?eHxG`;oDaOassshA#JuTMw{C)TMi&<%k@$s~E0b;zK7Hj<WKi%&y
zW|b+%$I~_ji1B(_tnsmjzUME-HpTdOT77^RucyTt&pwlm&}QANHpTdO+Liz@UQdfP
zzCC@nUukCH$vdXfZok=T^qVVEr8)abA$o87ra$PK3@ALx1YK=_dc6owb@q1qgH~lg
z;Xfv5wE^n&86h6{t84v1t23bR787*60qXS-A*P1^#2<8R1{A(wf^ITEz5XD?HDCI!
zKWI$`6rNy$HW;AZ+85$~@7?7G8o?Gx4BXlVhR;N<)N4MpEL9}hFqa&5d*EUY2(eDV
z(GkzNn30Yo<=9$*#|rHOV?(FL29l!_<7Xnb>44V4)J&mvLvgp-7@#w|<4Wa7f}k9$
zI|8`SAqA9R%P4Phq7{M}yjKzJs!Y2&YelhP1P@5sk0yqW+3kt3u``id4ZRknW<al_
z7z_?!AEAx&1hgANu`^n65kn3Ny^Yk-iqnA-S1WbpXr-<kt<=?7D=aol6{XHZY+aS&
zA_!^6c0RS85V8dGZk#GX-gQRa5^S!{<h8}e(Gz@vERtu_R2V#K4W4i+0-jUYiZ&_7
zzC-EFnv_yERSJ$egQGMxD~m&ywLom<M&8aFPmB$W4<sihZ?gLmHzv<ScI!~q)>Jw6
zh~9|3fUNs*S^wO-c@u2j2X$_QKXB&c)IKXa?OtUl8`cbVPfWm3cj&mGrKzyk4WmsM
zuFB;O2MfX+tU1__gC)DG5(C4d<40`F6%0j-%3;~7pb&k36p9{!f@+Q+T>uSc)t=J|
zj;>e=%~T*fYt6wV!vl`kXM$JwfUEeI13VS~v*~|9K?vUH1xE}t!RPvbt2megTn!B@
z3BJV(j#y{}gra#q;Eac=#!9_rIcv=jTe(qb&-jT`aD`KPm09yq9&VI90bgXLXBrxp
zSDZ@Y8q9la{^b}TAue@|jvq@5r`!KEhBAv0m%3y{A48=NYZ0Kx^+q=qInjq}E+sJB
zEO7<&7d?qn$;knP7b7r-mLo9t0GK76gikTBs2Ponr&x4x%6kqgGiis1OHBzh$3|V(
zkbuY@c>1K=Z%nAP^+Pz#OjgUVkr5C*6UoGx$e@m4%|Lr0MiNSKMW3@CsS1{EoRG$4
zmrS5lS58P%S58P%S7)u6;!1&q{oYeaS>rbtLKgtKSK1)E&b=X@5mKvj5;B^oD@PM`
z<!GX=7|K2}RSerz=_(W#qayaA%#-fKpC+TL?x3sojV8xntp=T|D2nRogLfoRakN*q
zw}%L^Q$kE6=>f*tL(vimL4&K>4@;-g))#1ZCyuDnI#+bD3eki*5(ft+#~HFzc#Z3d
zS~C%;4?@Bu+LaTv{K6_bacSYZBK_hn&mzg)C|w0UeE=DhrQHveZ<1%-C_958ce-B*
zD~^imr3Ynlumv4KFPcQC+$OG*yP`jXQoY8=3D4MnG?7R)+#gvi?)S)4ZmZT4zkr?O
zU+~-`-<@#d5I&D$k3N3Gi?CJH;dwd~5l`b{#p@yQjyP|94EY)CaDdu?6|9}j2uiKB
zZSc$1hFD`;JF0nSGYBNN?LrG_y(KO$;%&Iq+<HrGn<Q>%2SPn^HPps%1H7>qD78pU
zz-_99n&|KnL&?N2PTMC(lZnaE0c71bFn%OK(!}xOScy7G(L>2o17moE(|P<v@+SLa
za*~xel;*LI4<v_<CQjOe=su6x*AI-HO5n*#o4j%}4NoA)_(W3Wwi6yC00(FG0OLVH
zuo7Nir3+U0*tL%ijVA5HCsB}-_QZ+ABs+ThhSB6vO&7nybQJ^;P*9~0s-PKZC##or
z(Ja5VqS~4V28U2(1A_zdTb&Q%M&G^zov4TAU9DaFJHfJxmA#Wx{5r(GR&Z^tuP4x!
zWFsMJ2a$PT5T{MC7*1m``0{>LeKlmSv0ZhCCXSyNm`o^>3{FgpB?iWAiH*lUE>G*F
zIEb3UrsIe>13t8Q@ooHaz4uN09>ec#csle8{C*mB`3?;6Rs3#-a<Af-`VK`FA|XTi
zSeX8`#Jy-=_j>NMy%G`+fcAig20(EqXm@&O1mGSc^-qs-?VlbqE$)|4ha3yEqYLOo
zUi>>|Hex0F77LLhD$gtM&3}DG#5<3?`48QLd*g>Cy;jmU$nQ@}$`tZAM>Qh45O(M`
z+<7`YALk2@eplwcU4E}c?h$$XG|tWB&iOhQ<<<Cl&w00a8~cY2;nRyL&K`u#R`iI6
z#5K6?!F4;XZPAUh9#Fav5qaH#2-k>VO#=p_JVN0akr%}BX!PG0a6;m>852;NQL5jf
z9s4bo|GgglG+Dt%NPPeMSU{2oCkY(#kIKH!Mwt@gh5``T`yB_*N!UQ~+JgW3;{Th*
z^T44On};gZd$*2>`@y^ph1LJQ=tcA^=U-sXnI%5jckTP8Xf1oM?y|4$ZL@n?yE~gI
zSL2jtm9uBaJNrBP?2ZF_yE^-udUx&e5PJ{wwe9I?>ge3ndZ4S%b7Oz+zP>#@yPJA?
zdpbSjeVu&<LT7t#Pj7c`N2fuD27CAI>D{-d@7kuu*^E_}j}NZ}q?dK}UVXWufB8DT
znED-|KDjXvi&}Eu{ovl&jlPgQ7w2>d^l7`r0rd1XDC&RAoILT~D01<io1a3iVNY?}
zF+MwlF#|^j9T*=R#6N%!Azh2qhZU}Fj1pp!vKMzbemI1D`;h-*xVum0J}C3_W17AV
zcN>wq<v61s--q$(#=8;vG0)oLk$d^n-vXG>VoK|N@d1~Z#^n@J3sS13MWm*tL~43k
zOe0O<yahDj=ECXa<5yq-%fJYQO^V9kv?h*r@;#jYrkgyD1pZ>C=6jGwItYHTROcq&
z%yLG-as1`uvIQ<%;KOHuhw#WdgdUx<tq@v!ztwLw%Htp7&M)2+frn@3GK&ydX4sit
zY7`hfSXf&)jg^itK6kZptpkTONAhr0gsbd$D}+^^KDSkH2}LE17sW!S5y7M}x|y`F
z4~??Sd?h|KMnW?m3vANxJS&UFIhL6Z&snl)oco*kxGrJRXe*P3C7LW6EokPe@S)B1
zq0RH5aW%=r<LZ)0<0_g-<KK-;8dt+i8vhJr()iyVlg7X5m^A+L#-#C&G$xJzi!o`d
z4BESL6;I&Z610xG;+rOD7j<RfiltXqMYy7E)m0c*v=*=Eu`R;J_bqtz!B*6Q^YWGw
zyoHG^iZ$mPmawo#r3#s*JHR*H`dcBzR3w?u(OM;73ge2<7WkqrG#AiXK$$P00i{6~
zDQIMx?f{<yZDAZhCIxM|&}=^$Xg-2#0cEj-29yi^Mx48ArPjza-2pxax+o3(Mx0s~
zxzKEeQL!4|ECU}^L=j~8*kcW$TxgK6164vJ({u;;9O$s7H6w{K2wQUonosMZ09DY~
zn5sd;_OvxJO?QCLfiBjdi(P02g$y)nAu3;?Rf3mom3W}vh(|b7eiyhTKZU0-C0GN+
z@@DO9zEdkT-Gz5*InrpC79cAdl}Ksdgv&A9&@tYKt8ct22y#jdIS~{gEr)JW3NV$O
zSBy^Tie~1Mpk^f6Vi~S7wxGggI#9#~A}d3hVkK;uEIBnJ5E!KpWC10D$H$sRWf~CU
zU^6yAsETJ>XqKzP1fnxjTOI^z6xKyg(Pn5s44w*z8qG{=w5^3e&_Dx1C3tN#(*-KQ
zn~fN8GgA2s!NNwKl_BYwv!sHK$+$G1$r@o@cyVXBxODc}l9lRkUEg8jyN>W4Brn_S
zG@CNXY}9{LzQ~PspCdtN5CZW9N?sszoL-<g8qgdG#F$GUvT^UZ0%DKr0MXLrI#ARt
zh+?Hi4CuUKhoXSg6)TVj^#hCsi&sT?^()t4%cXvd^#o$vj7nzOuEMn)pMZEZ#XG3H
zkQEwa1t6Q{%kK!upsgU)l~+@~%F2Odl`Pj`=W3RmD`DB&5Qx#g8SKuL7nbkFa$sp!
z`~l0W?L19eb`A=Nw&Q=b99C+yKxgG26x40%z&?kSq;yySYek`>ssu1<Knr!C1sV`*
zHHwZ<0WFk3jB?Znj4luhvq%S8s1=5O7L_53`Yn<`j6MVcqXx8C2U?^7aX=82@rFQ)
zB@lxKfxxH%;k9I5^Dfqa=vmF&%Od;SB@zf15{U0JbBxWRE!BaRXh0l#C?IOI6hP{V
z8i7#*TBZXn)qv<h8CGO<Sy|zTL*BwHOS1~WzJS9TrLg)|eF41Ig<vsO=+c+F(itK-
z3d(rU3auDm)PPp%Kr1vL4xJPbEr6^pD~qv8XI<$QgT~_sD=H%{HCm<WWZC%^0)bHj
zvUQ+U1`u01kLR#dSPk2aS-Y^K&-skwk>sBAryq-mJm<0_7a@0~>FWLtnRADnSlGZb
z2ZbD?m^6-5Od5w6CaubcR_#Mu>qD#Yp{?_wVK+mT+*%*n1|Ql+AKE4#8lI$Q$=K{e
z<73{iF)_mqfGirf@0s~HJM_}%E;eY&UDOp@9w$R|7r2M(F7*A?y1Uq}%aWCq7)AG~
z!rW@<`?!!mVAO!{u0n4+T&)4I=9^_7NLWsbGrb=eH6V2RUZ6@1i0-21y#(ScAOp0<
z0IJe~)@VTVv8ap>>^Q3=5bXmdR~xoVbfjKws?s3JB6P~Aj1UB=c0n-o)<D+kAk`WO
zy*MggHOnHc)rtg04X8#3TB`xk^`kOY5U5520;2{5TX~CBqXDtKG^051UW+@o*-fg*
ziI<4FF!Y}FIt({?GsL|Srv_|dQLrnR^%6$;4%jpp?91?KvtEN?t5h%qt(C<hG;50+
zI#f&r0kws#9bTYXtypZz8K4bPAA%EzdqTWG8+D)!1`wktTO&1MG>FPq0jn!oi&a5b
zz-X|Wbl8m=EJKI_qS-e|{gm0k=EBhIbvn!@&0dT;8GY)cy;zsj2aGNd?X_74s?+qT
zLttr^8<JTt2Ck?qEc>9%QajOdYYSlkqrqa%=B<X!8Z4uo0wQd^)Q?reIV>1mAWn}l
z@#I7knWj6y=S=<TwE{C#HlqgBh^0XSD$UkFdoN%Fl_mj^X}SY^4q$@@$eLCDPYq)p
z3$7u6t066j7C{Y>X}SY^4q!|xGJ|wfz7d*$jna0kCKeg9H5ZU|3ylegOw%3Ua{wD%
z4WC0LOk$FIbI9qbJJUV&ZaiXSf4v94{Qju?G~+L&U*q6Z&X)Ph;|^@Tx`QK4_plJi
zFgV8R;6Mr?j^HeOB(g{x@npjq2p^CXb*QacF7vPdBm@zhJ#h5ScWM{om!*Ow<Ta<-
zq+dQhoEEs4LO2M~1N4J_@zQ4=eCy}EN6WwXS#$w4k3Y{4^Eo8?2Jg2Z(W|dQqBHj)
z%|*Hn31eXVBSgI~{&xIwo_aSDCA|zL4%^9VBbn#rpmmQ9O-`Jg7)jclHx4Dnq%5)d
z2aoK)d&FCCUyQ$gk?#_x-@cb4sQlKm&pdmsZdv)+`%wOkKYx_v{|*w{1k1k{iM?At
z5@(#lNHdYfkXVK!63cxv5~sGeBXPiZClc-RAd+bhkYrnA4r1}h^}7;6s0Zsq?avmo
zKq4efO16U&lgCbS>-I^B%G~-9!i0l+0Z(A3!=U(W{bn9lT)ObKj67JRuT18XG0!}O
z%Fh!v?`$7r`5{F2q8XU%<FJi)_j3rHX@|WdBcnqJ)g0|ueSM?tI+yG*+D1o4w#!+K
aY_WCHBkQ({pI!ObC-XMt<&3{!3;aKd<BGlj

literal 0
HcmV?d00001

diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java
index 1ec657dfe2..4d6468a819 100755
--- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java
+++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java
@@ -18,8 +18,10 @@
 
 package org.apache.poi.hssf.record.formula.functions;
 
+import junit.framework.AssertionFailedError;
 import junit.framework.TestCase;
 
+import org.apache.poi.hssf.HSSFTestDataSamples;
 import org.apache.poi.hssf.record.formula.AreaPtg;
 import org.apache.poi.hssf.record.formula.RefPtg;
 import org.apache.poi.hssf.record.formula.eval.Area2DEval;
@@ -31,6 +33,13 @@ import org.apache.poi.hssf.record.formula.eval.NumberEval;
 import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
 import org.apache.poi.hssf.record.formula.eval.StringEval;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.record.formula.functions.Countif.I_MatchPredicate;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue;
 
 /**
  * Test cases for COUNT(), COUNTA() COUNTIF(), COUNTBLANK()
@@ -146,4 +155,154 @@ public final class TestCountFuncs extends TestCase {
 		double result = NumericFunctionInvoker.invoke(new Countif(), args);
 		assertEquals(expected, result, 0);
 	}
+	
+	public void testCountIfEmptyStringCriteria() {
+		I_MatchPredicate mp;
+		
+		// pred '=' matches blank cell but not empty string
+		mp = Countif.createCriteriaPredicate(new StringEval("="));
+		confirmPredicate(false, mp, "");
+		confirmPredicate(true, mp, null);
+
+		// pred '' matches both blank cell but not empty string
+		mp = Countif.createCriteriaPredicate(new StringEval(""));
+		confirmPredicate(true, mp, "");
+		confirmPredicate(true, mp, null);
+		
+		// pred '<>' matches empty string but not blank cell 
+		mp = Countif.createCriteriaPredicate(new StringEval("<>"));
+		confirmPredicate(false, mp, null);
+		confirmPredicate(true, mp, "");
+	}
+	
+	public void testCountifComparisons() {
+		I_MatchPredicate mp;
+		
+		mp = Countif.createCriteriaPredicate(new StringEval(">5"));
+		confirmPredicate(false, mp, 4);
+		confirmPredicate(false, mp, 5);
+		confirmPredicate(true, mp, 6);
+		
+		mp = Countif.createCriteriaPredicate(new StringEval("<=5"));
+		confirmPredicate(true, mp, 4);
+		confirmPredicate(true, mp, 5);
+		confirmPredicate(false, mp, 6);
+		confirmPredicate(true, mp, "4.9");
+		confirmPredicate(false, mp, "4.9t");
+		confirmPredicate(false, mp, "5.1");
+		confirmPredicate(false, mp, null);
+
+		mp = Countif.createCriteriaPredicate(new StringEval("=abc"));
+		confirmPredicate(true, mp, "abc");
+		
+		mp = Countif.createCriteriaPredicate(new StringEval("=42"));
+		confirmPredicate(false, mp, 41);
+		confirmPredicate(true, mp, 42);
+		confirmPredicate(true, mp, "42");
+
+		mp = Countif.createCriteriaPredicate(new StringEval(">abc"));
+		confirmPredicate(false, mp, 4);
+		confirmPredicate(false, mp, "abc");
+		confirmPredicate(true, mp, "abd");
+
+		mp = Countif.createCriteriaPredicate(new StringEval(">4t3"));
+		confirmPredicate(false, mp, 4);
+		confirmPredicate(false, mp, 500);
+		confirmPredicate(true, mp, "500");
+		confirmPredicate(true, mp, "4t4");
+	}
+	
+	public void testWildCards() {
+		I_MatchPredicate mp;
+		
+		mp = Countif.createCriteriaPredicate(new StringEval("a*b"));
+		confirmPredicate(false, mp, "abc");
+		confirmPredicate(true, mp, "ab");
+		confirmPredicate(true, mp, "axxb");
+		confirmPredicate(false, mp, "xab");
+		
+		mp = Countif.createCriteriaPredicate(new StringEval("a?b"));
+		confirmPredicate(false, mp, "abc");
+		confirmPredicate(false, mp, "ab");
+		confirmPredicate(false, mp, "axxb");
+		confirmPredicate(false, mp, "xab");
+		confirmPredicate(true, mp, "axb");
+		
+		mp = Countif.createCriteriaPredicate(new StringEval("a~?"));
+		confirmPredicate(false, mp, "a~a");
+		confirmPredicate(false, mp, "a~?");
+		confirmPredicate(true, mp, "a?");
+		
+		mp = Countif.createCriteriaPredicate(new StringEval("~*a"));
+		confirmPredicate(false, mp, "~aa");
+		confirmPredicate(false, mp, "~*a");
+		confirmPredicate(true, mp, "*a");
+
+		mp = Countif.createCriteriaPredicate(new StringEval("12?12"));
+		confirmPredicate(false, mp, 12812);
+		confirmPredicate(true, mp, "12812");
+		confirmPredicate(false, mp, "128812");
+	}
+	public void testNotQuiteWildCards() {
+		I_MatchPredicate mp;
+	
+		// make sure special reg-ex chars are treated like normal chars 
+		mp = Countif.createCriteriaPredicate(new StringEval("a.b"));
+		confirmPredicate(false, mp, "aab");
+		confirmPredicate(true, mp, "a.b");
+
+		
+		mp = Countif.createCriteriaPredicate(new StringEval("a~b"));
+		confirmPredicate(false, mp, "ab");
+		confirmPredicate(false, mp, "axb");
+		confirmPredicate(false, mp, "a~~b");
+		confirmPredicate(true, mp, "a~b");
+		
+		mp = Countif.createCriteriaPredicate(new StringEval(">a*b"));
+		confirmPredicate(false, mp, "a(b");
+		confirmPredicate(true, mp, "aab");
+		confirmPredicate(false, mp, "a*a");
+		confirmPredicate(true, mp, "a*c");
+	}
+	
+	private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, int value) {
+		assertEquals(expectedResult, matchPredicate.matches(new NumberEval(value)));
+	}
+	private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, String value) {
+		Eval ev = value == null ? (Eval)BlankEval.INSTANCE : new StringEval(value); 
+		assertEquals(expectedResult, matchPredicate.matches(ev));
+	}
+	
+	public void testCountifFromSpreadsheet() {
+		final String FILE_NAME = "countifExamples.xls";
+		final int START_ROW_IX = 1;
+		final int COL_IX_ACTUAL = 2;
+		final int COL_IX_EXPECTED = 3;
+		
+		int failureCount = 0;
+		HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
+		HSSFSheet sheet = wb.getSheetAt(0);
+		HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
+		int maxRow = sheet.getLastRowNum();
+		for (int rowIx=START_ROW_IX; rowIx<maxRow; rowIx++) {
+			HSSFRow row = sheet.getRow(rowIx);
+			if(row == null) {
+				continue;
+			}
+			HSSFCell cell = row.getCell(COL_IX_ACTUAL);
+			fe.setCurrentRow(row);
+			CellValue cv = fe.evaluate(cell);
+			double actualValue = cv.getNumberValue();
+			double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
+			if (actualValue != expectedValue) {
+				System.err.println("Problem with test case on row " + (rowIx+1) + " "
+						+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
+				failureCount++;
+			}
+		}
+		
+		if (failureCount > 0) {
+			throw new AssertionFailedError(failureCount + " countif evaluations failed. See stderr for more details");
+		}
+	}
 }
-- 
2.39.5