Help COUNTIF

[Guest]

Formula COUNTIF(C20:C39;"A") + COUNTIF(C20:C39;"B")

Error in formula COUNTIF(C20:C39;"A") + COUNTIF(C20:C39;"B")
Wrong number of arguments for function COUNTIF.
Error in EncodeFormulaII2.pas Line 526 and 571

[larsa]

Hello

You are using the wrong character (semicolon) as argument separator. Please use the delphi global variable ListSeparator.
(The error message maybe not is so clear, but we will imporve this in the next update).


Lars Arvidsson

[Guest]

I've also seen this type of error,

I've modified the patched the TEncodeFormula.GetNextFuncArg function in
encodeformulaII2.pas

Formula COUNTIF(C20:C39;"A") + COUNTIF(C20:C39;"B")

the original function finds 3 arguments for the countIf this because the closing ) of the countif is not recognised.

Maybe this solves your problems as well


function TEncodeFormula.GetNextFuncArg(Index: integer): integer;
var
Par1,Par2,i: integer;
begin
Par1 := 0;
Par2 := 0;
for i := Index to FTokenizer.Count - 1 do begin
Result := i;
case FTokenizer[Result].TokenType of
ttFunc,
ttParenL: Inc(Par1);
ttParenR: Dec(Par1);
ttVectL : Inc(Par2);
ttVectR : Dec(Par2);
// '[': Inc(Par3);
// ']': Dec(Par3);
end;
// ---> Informaticamp added
if (Par1<0) then begin
Result := -1;
exit;
end // <--- Informaticamp added (
else if (Par1 = 0) and (Par2 = 0) and (FTokenizer[Result].TokenType = ttListSep) then
Exit;
end;
Result := -1;
end;

[Guest]

Maybe useful to users using a lot of formula's in XLSreadwriteII 3.0 (but not fully tested)

--> example formula not working correctly (replace ; by your system listseparator)

IF(A2+A3+A4+A5+A6>0;((A5+A6)*100)/(A2+A3+A4+A5+A6);0)

ps don't forget to fill A2..A6 with a float value > 0


The A2+A3+A4+A5+A6>0 is never TRUE

I've traced this to the CalculateFormula2.pas -> function TVarStack.Operator(Op: Byte);

There seems to be a problem initialising variables before using them

I've added some code that worked for me....

begin
if FStackPtr < 0 then
raise Exception.Create('Value is missing');
FVSetNull(FVResult);

F1 := 0;
F2 := 0;

if OP in [ptgAdd,ptgSub,ptgMul,ptgDiv,ptgPower,ptgConcat,ptgUPlus,ptgUMinus,ptgPercent] then begin
FV1 := GetValue(FStack[FStackPtr]);
F1 := GetFloatValue(FV1);
if (FVResult.ValType <> fvError) and (not (Op in [ptgUPlus,ptgUMinus,ptgPercent])) then begin
FV2 := GetValue(FStack[FStackPtr - 1]);
F2 := GetFloatValue(FV2);
end;
// Informaticamp ---> not checked for other operators these need at least 2 arguments
end else if OP in [ptgLT,ptgLE,ptgEQ,ptgGE,ptgGT,ptgNE] then begin//
FV1 := GetValue(FStack[FStackPtr]);
if FStackPtr > 0 then
FV2 := GetValue(FStack[FStackPtr - 1])
else
FVSetNull(FV2);
end else begin
FVSetNull(FV1);
FVSetNull(FV2);
end; // <-- Informaticamp Initialize the vars