public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 3 || args.Length % 2 == 0)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
AreaEval minRange = Sumifs.ConvertRangeArg(args[0]);
// collect pairs of ranges and criteria
AreaEval[] ae = new AreaEval[(args.Length - 1) / 2];
IMatchPredicate[] mp = new IMatchPredicate[ae.Length];
for (int i = 1, k = 0; i < args.Length; i += 2, k++)
{
ae[k] = Sumifs.ConvertRangeArg(args[i]);
mp[k] = Countif.CreateCriteriaPredicate(args[i + 1], ec.RowIndex, ec.ColumnIndex);
}
Sumifs.ValidateCriteriaRanges(ae, minRange);
Sumifs.ValidateCriteria(mp);
double result = Sumifs.CalcMatchingCells(ae, mp, minRange, double.NaN, (init, current) => !current.HasValue ? init : double.IsNaN(init) ? current.Value : current.Value < init ? current.Value : init);
return(new NumberEval(result));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
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 TestCriteriaPredicateNe_Bug46647()
{
IMatchPredicate mp = Countif.CreateCriteriaPredicate(new StringEval("<>aa"), 0, 0);
Assert.IsNotNull(mp);
StringEval seA = new StringEval("aa"); // this should not match the criteria '<>aa'
StringEval seB = new StringEval("bb"); // this should match
if (mp.Matches(seA) && !mp.Matches(seB))
{
throw new AssertionException("Identified bug 46647");
}
Assert.IsFalse(mp.Matches(seA));
Assert.IsTrue(mp.Matches(seB));
// general Tests for not-equal (<>) operator
AreaEval range;
ValueEval[] values;
values = new ValueEval[] {
new StringEval("aa"),
new StringEval("def"),
new StringEval("aa"),
new StringEval("ghi"),
new StringEval("aa"),
new StringEval("aa"),
};
range = EvalFactory.CreateAreaEval("A1:A6", values);
ConfirmCountIf(2, range, new StringEval("<>aa"));
values = new ValueEval[] {
new StringEval("ab"),
new StringEval("aabb"),
new StringEval("aa"), // match
new StringEval("abb"),
new StringEval("aab"),
new StringEval("ba"), // match
};
range = EvalFactory.CreateAreaEval("A1:A6", values);
ConfirmCountIf(2, range, new StringEval("<>a*b"));
values = new ValueEval[] {
new NumberEval(222),
new NumberEval(222),
new NumberEval(111),
new StringEval("aa"),
new StringEval("111"),
};
range = EvalFactory.CreateAreaEval("A1:A5", values);
ConfirmCountIf(4, range, new StringEval("<>111"));
}
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 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");
}
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 TestCountifAreaCriteria()
{
int srcColIx = 2; // anything but column A
ValueEval v0 = new NumberEval(2.0);
ValueEval v1 = new StringEval("abc");
ValueEval v2 = ErrorEval.DIV_ZERO;
AreaEval ev = EvalFactory.CreateAreaEval("A10:A12", new ValueEval[] { v0, v1, v2, });
I_MatchPredicate mp;
mp = Countif.CreateCriteriaPredicate(ev, 9, srcColIx);
ConfirmPredicate(true, mp, srcColIx);
ConfirmPredicate(false, mp, "abc");
ConfirmPredicate(false, mp, ErrorEval.DIV_ZERO);
mp = Countif.CreateCriteriaPredicate(ev, 10, srcColIx);
ConfirmPredicate(false, mp, srcColIx);
ConfirmPredicate(true, mp, "abc");
ConfirmPredicate(false, mp, ErrorEval.DIV_ZERO);
mp = Countif.CreateCriteriaPredicate(ev, 11, srcColIx);
ConfirmPredicate(false, mp, srcColIx);
ConfirmPredicate(false, mp, "abc");
ConfirmPredicate(true, mp, ErrorEval.DIV_ZERO);
ConfirmPredicate(false, mp, ErrorEval.VALUE_INVALID);
// tricky: indexing outside of A10:A12
// even this #VALUE! error Gets used by COUNTIF as valid criteria
mp = Countif.CreateCriteriaPredicate(ev, 12, srcColIx);
ConfirmPredicate(false, mp, srcColIx);
ConfirmPredicate(false, mp, "abc");
ConfirmPredicate(false, mp, ErrorEval.DIV_ZERO);
ConfirmPredicate(true, mp, ErrorEval.VALUE_INVALID);
}
private static I_MatchPredicate CreateCriteriaPredicate(ValueEval ev)
{
return(Countif.CreateCriteriaPredicate(ev, 0, 0));
}