获取"公式解析错误";输入这个之后。不确定我是否遗漏了逗号或圆括号。我想我遵循了IFS()， AND()和COUNTIFS()的规则。也不确定是否有更简单的方法来编写这个公式，或者是否最好使用Google Apps Script。
我注意到这个公式只有在我为IFS()有1对参数时才有效(因此这将是第一个AND()和第一个COUNTIFS()，但是当我试图添加另一对参数时，然后我得到公式解析错误。
将COUNTIFS()放入IF()中没有任何问题，但由于IF()的限制，我不得不尝试IFS()，以便我可以处理更具体的条件组合。
IFS
(
AND($S$1 = "Include Both", $V$1 = "Any", $S$2 = "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2
),
AND($S$1 = "Include Both", $V$1 != "Any", $S$2 = "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$O$3:$O"), "="&$V$1,
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1 ,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2
),
AND($S$1 = "Include Both", $V$1 = "Any", $S$2 != "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1 ,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2,
INDIRECT("'Activity Log'!$H$3:$H"), "="&$S$2
),
AND($S$1 = "Include Both", $V$1 != "Any", $S$2 != "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$O$3:$O"), "="&$V$1,
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2,
INDIRECT("'Activity Log'!$H$3:$H"), "="&$S$2
),
AND($S$1 != "Include Both", $V$1 = "Any", $S$2 = "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2,
INDIRECT("'Activity Log'!$M$3:$M"), "="&$S$1
),
AND($S$1 != "Include Both", $V$1 != "Any", $S$2 = "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$O$3:$O"), "="&$V$1,
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2,
INDIRECT("'Activity Log'!$M$3:$M"), "="&$S$1
),
AND($S$1 != "Include Both", $V$1 != "Any", $S$2 != "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$O$3:$O"), "="&$V$1,
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2,
INDIRECT("'Activity Log'!$M$3:$M"), "="&$S$1,
INDIRECT("'Activity Log'!$H$3:$H"), "="&$S$2
),
AND($S$1 != "Include Both", $V$1 = "Any", $S$2 != "All"),
COUNTIFS
(
INDIRECT("'Activity Log'!$A$3:$A"), $N6,
INDIRECT("'Activity Log'!$E$3:$E"), "="&U$3,
INDIRECT("'Activity Log'!$I$3:$I"), ">="&$N$1,
INDIRECT("'Activity Log'!$I$3:$I"), "<="&$N$2,
INDIRECT("'Activity Log'!$M$3:$M"), "="&$S$1,
INDIRECT("'Activity Log'!$H$3:$H"), "="&$S$2
)
)
!=不支持比较运算符，您应该使用<>
如果A和/或E是数字。请去掉单引号
。"A='",N6,"' and ",→"A=",N6," and ",
=QUERY(INDIRECT("'Activity Log'!A3:O"),CONCATENATE("select count(A) where ",
"A='",N6,"' and ",
"E='",U3,"' and ",
"I>=",N1," and ",
"I<=",N2,
IF(V1="Any",," and O='"&V1&"'"),
IF(S2="All",," and H='"&S2&"'"),
IF(S1="Include Both",," and M='"&S1&"'"),
" label count(A) ''"),0)