如果在一个单元格中选择了选中的选项，如何计算值

发布于 2025-01-12 19:53:37 字数 725 浏览 0 评论 0原文

我有一个电子表格，用户可以在其中填写表格，他们可以通过单击复选框来选择多个选项。

回复表如下所示

我想按选项过滤结果，所以我的主要目标是获取所选选项的总数：

我一直在尝试使用 COUNTIF 但它不起作用，因为我没有选择

=COUNTIF('Form Responses 1'!C2:F4,"*"&$B$1&"*")

我假设需要添加 VLOOKUP但我不确定如何将其与选项

Here's a 电子表格相匹配尝试一下

原文

I have a spreadsheet where users fill out a form, they have multiple options they can pick by clicking the checkboxes.

The responses sheets looks like this

I want to filter the results by option, so my main goal is to get the total amount of options selected:

I've been trying with COUNTIF but it's not working because I'm not selecting the specific response

=COUNTIF('Form Responses 1'!C2:F4,"*"&$B$1&"*")

I'm assuming I need to add a VLOOKUP but I'm not sure how to match it with the option

Here's a spreadsheet to play around with it

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

皇甫轩 2025-01-19 19:53:37

尝试：

=INDEX({QUERY(TRIM(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B, ",")), "×")), 
 "select Col1,count(Col1) where Col2 is not null group by Col1 pivot Col2 label Col1 'Person'"); 
 {"Total", TRANSPOSE(MMULT(TRANSPOSE(QUERY(QUERY(TRIM(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B, ",")), "×")), 
 "select count(Col1) where Col2 is not null group by Col1 pivot Col2"), "offset 1", )*1), 
 SEQUENCE(COUNTUNIQUE(TRIM(FLATTEN(SPLIT(TEXTJOIN(",", 1, B2:B), ",")))), 1, 1, )))}})

try:

=INDEX({QUERY(TRIM(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B, ",")), "×")), 
 "select Col1,count(Col1) where Col2 is not null group by Col1 pivot Col2 label Col1 'Person'"); 
 {"Total", TRANSPOSE(MMULT(TRANSPOSE(QUERY(QUERY(TRIM(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B, ",")), "×")), 
 "select count(Col1) where Col2 is not null group by Col1 pivot Col2"), "offset 1", )*1), 
 SEQUENCE(COUNTUNIQUE(TRIM(FLATTEN(SPLIT(TEXTJOIN(",", 1, B2:B), ",")))), 1, 1, )))}})

回复收藏 0 原文

夜光 2025-01-19 19:53:37

也许是这样的公式：

=IFNA(QUERY({$A$2:$A$6, $B$2:$B$6}, "Select 1 where Col2 contains '"&B$9&"' and Col1 = '"&$A10&"' order by Col1 label 1 ''"), 0)

如果引用的单元格包含指定的文本，则输出 1，否则输出 0。请参阅有关查询函数和查询语言了解更多信息。

Perhaps a formula like this:

=IFNA(QUERY({$A$2:$A$6, $B$2:$B$6}, "Select 1 where Col2 contains '"&B$9&"' and Col1 = '"&$A10&"' order by Col1 label 1 ''"), 0)

This outputs a 1 if the referenced cell contains the specified text, and a 0 otherwise. See the docs for the Query Function and the Query Language for more information.

回复收藏 0 原文

~没有更多了~