SUMIFS function #152
Description
Hello,
I wrote a custom function like "SUMIFS" in excel. It works as expected, but when i save the file it cannot be opened in excel. I think the main reason is excel uses semicolon as seperator between arguments but reogrid uses comma. Is there a way to save custom functions excel compatible?
Sample usage of formula:
=SUMIFS(E1:E8,A1:A8,"True",B1:B8,"OK",C1:C8,"NaN",D1:D8,"GASSED")
First argument is the sum range. The other arguments are range - criteria pairs.
Here my custom function:
FormulaExtension.CustomFunctions["SUMIFS"] = (cell, args) =>
{
if (args.Length == 0)
{
return null;
}
try
{
double result = 0;
var sumRange = args[0].ToString();
var rangeLetters = new List<string>();
var criterias = new List<string>();
for (int i = 1; i < args.Length; i+=2)
{
rangeLetters.Add(args[i].ToString()[0].ToString());
}
for (int i = 2; i < args.Length; i+=2)
{
criterias.Add(args[i].ToString());
}
bool criteriaTest = true;
cell.Worksheet.IterateCells(sumRange, (row, col, c) =>
{
for (int i = 0; i < criterias.Count; i++)
{
criteriaTest = (string)cell.Worksheet.Cells[rangeLetters[i] + (row + 1)].Data == criterias[i];
if (!criteriaTest)
{
break;
}
}
result = criteriaTest ? result + (double)c.Data : result;
criteriaTest = true;
return true;
});
return result;
}
catch (Exception ex)
{
return null;
}
};