private static object CountIf(List <Expression> p)
{
CalcEngine ce = new CalcEngine();
var cnt = 0.0;
long processedCount = 0;
if (p[0] is XObjectExpression ienum)
{
long totalCount = CalcEngineHelpers.GetTotalCellsCount(ienum);
var criteria = p[1].Evaluate();
foreach (var value in ienum)
{
if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce))
{
cnt++;
}
processedCount++;
}
// Add count of empty cells outside the used range if they match criteria
if (CalcEngineHelpers.ValueSatisfiesCriteria(string.Empty, criteria, ce))
{
cnt += (totalCount - processedCount);
}
}
return(cnt);
}
private static object CountIfs(List <Expression> p)
{
// get parameters
var ce = new CalcEngine();
long count = 0;
int numberOfCriteria = p.Count / 2;
long totalCount = 0;
// prepare criteria-parameters:
var criteriaRanges = new Tuple <object, List <object> > [numberOfCriteria];
for (int criteriaPair = 0; criteriaPair < numberOfCriteria; criteriaPair++)
{
var criteriaRange = p[criteriaPair * 2] as XObjectExpression;
var criterion = p[(criteriaPair * 2) + 1].Evaluate();
var criteriaRangeValues = new List <object>();
foreach (var value in criteriaRange)
{
criteriaRangeValues.Add(value);
}
criteriaRanges[criteriaPair] = new Tuple <object, List <object> >(
criterion,
criteriaRangeValues);
if (totalCount == 0)
{
totalCount = CalcEngineHelpers.GetTotalCellsCount(criteriaRange);
}
}
long processedCount = 0;
for (var i = 0; i < criteriaRanges[0].Item2.Count; i++)
{
if (criteriaRanges.All(criteriaPair => CalcEngineHelpers.ValueSatisfiesCriteria(
criteriaPair.Item2[i], criteriaPair.Item1, ce)))
{
count++;
}
processedCount++;
}
// Add count of empty cells outside the used range if they match criteria
if (criteriaRanges.All(criteriaPair => CalcEngineHelpers.ValueSatisfiesCriteria(
string.Empty, criteriaPair.Item1, ce)))
{
count += (totalCount - processedCount);
}
// done
return(count);
}
private static object CountBlank(List <Expression> p)
{
if ((p[0] as XObjectExpression)?.Value as CellRangeReference == null)
{
throw new NoValueAvailableException("COUNTBLANK should have a single argument which is a range reference");
}
var e = p[0] as XObjectExpression;
long totalCount = CalcEngineHelpers.GetTotalCellsCount(e);
long nonBlankCount = 0;
foreach (var value in e)
{
if (!CalcEngineHelpers.ValueIsBlank(value))
{
nonBlankCount++;
}
}
return(0d + totalCount - nonBlankCount);
}