问在Google工作表中以分隔字符串计算来宾数

EN

您可以通过以下操作获得总金额：

=if(regexmatch(A1,"\+"),sum(ArrayFormula(query(split(transpose(split(A1,",")),"\+"),"select count(Col1), sum(Col2)",0))),if(isblank(A1),"",counta(split(A1,","))))

​

​

解释：

如果答案中没有

• if(regexmatch(A1,"\+"), something, if(isblank(A1),"",counta(split(A1,",")))) =>符号，则检查单元格是否为空，如果为空，则打印为空，否则只需计算逗号之间有多少人，否则用加号计算。(解释below)
• split(A1,",")),"\+") =>红色区域=>将用逗号分隔单元格，结果可以在picture
• split(TRANSPOSE(split(A1,",")),"\+") =>绿色区域的红色区域中看到，=>将循环遍历上面的每个结果，并分离到右边的一个单元格，在它们之间有一个+号的值，可以在image
• query(split(TRANSPOSE(split(A1,",")),"\+"),"select count(Col1), sum(Col2)",0) =>蓝色区域=>中的绿色区域中看到，然后我们将查询2列，在左边的2列中，我们希望计数该列中的行数(列中有名称)，在下一列中，我们希望对值进行和(加号ones)
• sum(ArrayFormula(query(split(transpose(split(A1,",")),"\+"),"select count(Col1), sum(Col2)",0))) =>黄色区域=> )，然后我们将对2列的值进行求和，以得到最终结果

您可以使用以下公式

=IF(LEN(A2), 
     SUM(COUNTA(SPLIT(A2,",")), 
         IFERROR(SPLIT(REGEXREPLACE(A2,"\D"," ")," "))),"")

​

​

所使用的职能：

• IF
• LEN
• SUM
• COUNTA
• SPLIT
• IFERROR
• REGEXREPLACE