IFS公式中的多个COUNTIFS (公式解析错误)
IFS公式中的多个COUNTIFS (公式解析错误)
提问于 2021-08-09 00:47:27
输入后得到“公式分析错误”。不知道我是不是漏掉了逗号或括号。我想我在遵守IFS()，和()和COUNTIFS()的规则。另外，也不确定是否有一种更简单的方法来编写这个公式，或者是否最好使用Google脚本。
我注意到，只有当我为IFS()设置了1对参数(所以这将是第一个和()和第一个COUNTIFS() )时，这个公式才能工作，但是当我尝试添加另一对参数时，我就会得到公式解析错误。
我在IF()中放置COUNTIFS()没有问题，但是由于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
)
)Stack Overflow用户
回答已采纳
发布于 2021-08-09 01:17:25
不支持!=比较运算符，应使用<>
如果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)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68705780
复制相关文章
相似问题
腾讯云开发者
