diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 12ea4e7..43fce93 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -70,6 +70,7 @@ ce.RegisterFunction("SUBTOTAL", 2, 255, Subtotal); ce.RegisterFunction("SUM", 1, int.MaxValue, Sum); ce.RegisterFunction("SUMIF", 2, 3, SumIf); + ce.RegisterFunction("SUMIFS", 3, 255, SumIfs); ce.RegisterFunction("SUMPRODUCT", 1, 30, SumProduct); ce.RegisterFunction("SUMSQ", 1, 255, SumSq); //ce.RegisterFunction("SUMX2MY2", SumX2MY2, 1); @@ -288,9 +289,9 @@ private static object SumIf(List p) { // get parameters - var range = p[0] as IEnumerable; - var sumRange = p.Count < 3 ? range : p[2] as IEnumerable; - var criteria = p[1].Evaluate(); + var range = p[0] as IEnumerable; // range of values to match the criteria against + var sumRange = p.Count < 3 ? range : p[2] as IEnumerable; // range of values to sum up + var criteria = p[1].Evaluate(); // the criteria to evaluate // build list of values in range and sumRange var rangeValues = new List(); @@ -319,6 +320,63 @@ return tally.Sum(); } + private static object SumIfs(List p) + { + // get parameters + var sumRange = p[0] as IEnumerable; + + var sumRangeValues = new List(); + foreach (var value in sumRange) + { + sumRangeValues.Add(value); + } + + var ce = new CalcEngine(); + var tally = new Tally(); + + int numberOfCriteria = p.Count / 2; // int division returns floor() automatically, that's what we want. + + // prepare criteria-parameters: + var criteriaRanges = new Tuple>[numberOfCriteria]; + for(int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) + { + var criterion = p[criteriaPair * 2 + 1].Evaluate(); + var criteriaRange = p[(criteriaPair + 1) * 2] as IEnumerable; + var criteriaRangeValues = new List(); + foreach (var value in criteriaRange) + { + criteriaRangeValues.Add(value); + } + + criteriaRanges[criteriaPair] = new Tuple>( + criterion, + criteriaRangeValues); + } + + for (var i = 0; i < sumRangeValues.Count; i++) + { + bool shouldUseValue = true; + + foreach(var criteriaPair in criteriaRanges) + { + if (!CalcEngineHelpers.ValueSatisfiesCriteria( + criteriaPair.Item2[i], + criteriaPair.Item1, + ce)) + { + shouldUseValue = false; + break; // we're done with the inner loop as we can't ever get true again. + } + } + + if (shouldUseValue) + tally.AddValue(sumRangeValues[i]); + } + + // done + return tally.Sum(); + } + private static object SumProduct(List p) { // all parameters should be IEnumerable diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 188dbb4..58baace 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -24,6 +24,7 @@ ce.RegisterFunction("COUNTA", 1, int.MaxValue, CountA); ce.RegisterFunction("COUNTBLANK", 1, CountBlank); ce.RegisterFunction("COUNTIF", 2, CountIf); + ce.RegisterFunction("COUNTIFS", 2, 144, CountIfs); //COVAR Returns covariance, the average of the products of paired deviations //CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value //DEVSQ Returns the sum of squares of deviations @@ -135,12 +136,14 @@ } return cnt; } + internal static bool IsBlank(object value) { return value == null || value is string && ((string)value).Length == 0; } + static object CountIf(List p) { CalcEngine ce = new CalcEngine(); @@ -157,14 +160,66 @@ } return cnt; } + + private static object CountIfs(List p) + { + // get parameters + var ce = new CalcEngine(); + int count = 0; + + int numberOfCriteria = p.Count / 2; + + // prepare criteria-parameters: + var criteriaRanges = new Tuple>[numberOfCriteria]; + for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++) + { + var criteriaRange = p[criteriaPair * 2] as IEnumerable; + var criterion = p[(criteriaPair * 2) + 1].Evaluate(); + var criteriaRangeValues = new List(); + foreach (var value in criteriaRange) + { + criteriaRangeValues.Add(value); + } + + criteriaRanges[criteriaPair] = new Tuple>( + criterion, + criteriaRangeValues); + } + + for (var i = 0; i < criteriaRanges[0].Item2.Count; i++) + { + bool shouldUseValue = true; + + foreach (var criteriaPair in criteriaRanges) + { + if (!CalcEngineHelpers.ValueSatisfiesCriteria( + criteriaPair.Item2[i], + criteriaPair.Item1, + ce)) + { + shouldUseValue = false; + break; // we're done with the inner loop as we can't ever get true again. + } + } + + if (shouldUseValue) + count++; + } + + // done + return count; + } + static object Max(List p) { return GetTally(p, true).Max(); } + static object MaxA(List p) { return GetTally(p, false).Max(); } + static object Min(List p) { return GetTally(p, true).Min(); diff --git a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs index ad9e5dc..8c95b10 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/MathTrigTests.cs @@ -293,6 +293,133 @@ } } + /// + /// refers to Example 1 to SumIf from the Excel documentation. + /// As SumIfs should behave the same if called with three parameters, we can take that example here again. + /// + /// + /// + /// + [TestCase(63000, "SUMIFS(B1:B4, \">160000\", A1:A4)")] + [TestCase(21000, "SUMIFS(B1:B4, 300000, A1:A4)")] + [TestCase(28000, "SUMIFS(B1:B4, \">\" &C1, A1:A4)")] + public void SumIfs_ReturnsCorrectValues_ReferenceExampleForSumIf1FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(1, 1).Value = 100000; + ws.Cell(1, 2).Value = 7000; + ws.Cell(2, 1).Value = 200000; + ws.Cell(2, 2).Value = 14000; + ws.Cell(3, 1).Value = 300000; + ws.Cell(3, 2).Value = 21000; + ws.Cell(4, 1).Value = 400000; + ws.Cell(4, 2).Value = 28000; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + + /// + /// refers to Example 2 to SumIf from the Excel documentation. + /// As SumIfs should behave the same if called with three parameters, we can take that example here again. + /// + /// + /// + /// + [TestCase(2000, "SUMIFS(C2:C7, \"Fruits\", A2:A7)")] + [TestCase(12000, "SUMIFS(C2:C7, \"Vegetables\", A2:A7)")] + [TestCase(4300, "SUMIFS(C2:C7, \"*es\", B2:B7)")] + [TestCase(400, "SUMIFS(C2:C7, \"\", A2:A7)")] + public void SumIfs_ReturnsCorrectValues_ReferenceExample2FromMicrosoft(int expectedOutcome, string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell(2, 1).Value = "Vegetables"; + ws.Cell(3, 1).Value = "Vegetables"; + ws.Cell(4, 1).Value = "Fruits"; + ws.Cell(5, 1).Value = ""; + ws.Cell(6, 1).Value = "Vegetables"; + ws.Cell(7, 1).Value = "Fruits"; + + ws.Cell(2, 2).Value = "Tomatoes"; + ws.Cell(3, 2).Value = "Celery"; + ws.Cell(4, 2).Value = "Oranges"; + ws.Cell(5, 2).Value = "Butter"; + ws.Cell(6, 2).Value = "Carrots"; + ws.Cell(7, 2).Value = "Apples"; + + ws.Cell(2, 3).Value = 2300; + ws.Cell(3, 3).Value = 5500; + ws.Cell(4, 3).Value = 800; + ws.Cell(5, 3).Value = 400; + ws.Cell(6, 3).Value = 4200; + ws.Cell(7, 3).Value = 1200; + + ws.Cell(1, 3).Value = 300000; + + Assert.AreEqual(expectedOutcome, (double)ws.Evaluate(formula)); + } + } + + /// + /// refers to example data and formula to SumIfs in the Excel documentation, + /// + /// + [TestCase(20, "=SUMIFS(A2:A9, B2:B9, \"=A*\", C2:C9, \"Tom\")")] + [TestCase(30, "=SUMIFS(A2:A9, B2:B9, \"<>Bananas\", C2:C9, \"Tom\")")] + public void SumIfs_ReturnsCorrectValues_ReferenceExampleFromMicrosoft( + int result, + string formula) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).Value = 5; + ws.Cell(1, 2).Value = "Apples"; + ws.Cell(1, 3).Value = "Tom"; + + ws.Cell(2, 1).Value = 4; + ws.Cell(2, 2).Value = "Apples"; + ws.Cell(2, 3).Value = "Sarah"; + + ws.Cell(3, 1).Value = 15; + ws.Cell(3, 2).Value = "Artichokes"; + ws.Cell(3, 3).Value = "Tom"; + + ws.Cell(4, 1).Value = 3; + ws.Cell(4, 2).Value = "Artichokes"; + ws.Cell(4, 3).Value = "Sarah"; + + ws.Cell(5, 1).Value = 22; + ws.Cell(5, 2).Value = "Bananas"; + ws.Cell(5, 3).Value = "Tom"; + + ws.Cell(6, 1).Value = 12; + ws.Cell(6, 2).Value = "Bananas"; + ws.Cell(6, 3).Value = "Sarah"; + + ws.Cell(7, 1).Value = 10; + ws.Cell(7, 2).Value = "Carrots"; + ws.Cell(7, 3).Value = "Tom"; + + ws.Cell(8, 1).Value = 33; + ws.Cell(8, 2).Value = "Carrots"; + ws.Cell(8, 3).Value = "Sarah"; + } + } + + [Test] public void SumProduct() { diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs index 24931ed..a0bb2a2 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -134,6 +134,74 @@ } } + [TestCase("=COUNTIFS(B1:D1, \"=Yes\")", 1)] + [TestCase("=COUNTIFS(B1:B4, \"=Yes\", C1:C4, \"=Yes\")", 2)] + [TestCase("= COUNTIFS(B4:D4, \"=Yes\", B2:D2, \"=Yes\")", 1)] + public void CountIfs_ReferenceExample1FromExcelDocumentations( + string formula, + int expectedOutcome) + { + using (var wb = new XLWorkbook()) + { + wb.ReferenceStyle = XLReferenceStyle.A1; + + var ws = wb.AddWorksheet("Sheet1"); + + ws.Cell(1, 1).Value = "Davidoski"; + ws.Cell(1, 2).Value = "Yes"; + ws.Cell(1, 3).Value = "No"; + ws.Cell(1, 4).Value = "No"; + + ws.Cell(2, 1).Value = "Burke"; + ws.Cell(2, 2).Value = "Yes"; + ws.Cell(2, 3).Value = "Yes"; + ws.Cell(2, 4).Value = "No"; + + ws.Cell(3, 1).Value = "Sundaram"; + ws.Cell(3, 2).Value = "Yes"; + ws.Cell(3, 3).Value = "Yes"; + ws.Cell(3, 4).Value = "Yes"; + + ws.Cell(4, 1).Value = "Levitan"; + ws.Cell(4, 2).Value = "No"; + ws.Cell(4, 3).Value = "Yes"; + ws.Cell(4, 4).Value = "Yes"; + + Assert.AreEqual(expectedOutcome, (int)ws.Evaluate(formula)); + } + } + + [Test] + public void CountIfs_SingleCondition() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTIFS(D3:D45,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = ws.Evaluate(@"=COUNTIFS(D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = workbook.Evaluate(@"=COUNTIFS(Data!D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + } + + [TestCase(@"=COUNTIFS(Data!E:E, ""J*"")", 13)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*i*"")", 21)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*in*"")", 9)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*i*l"")", 9)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*i?e*"")", 9)] + [TestCase(@"=COUNTIFS(Data!E:E, ""*o??s*"")", 10)] + [TestCase(@"=COUNTIFS(Data!X1:X1000, """")", 1000)] + [TestCase(@"=COUNTIFS(Data!E1:E44, """")", 1)] + public void CountIfs_SingleConditionWithWildcards(string formula, int expectedResult) + { + var ws = workbook.Worksheets.First(); + + int value = ws.Evaluate(formula).CastTo(); + Assert.AreEqual(expectedResult, value); + } + [OneTimeTearDown] public void Dispose() {