计算单元格中的值数，除非为空 [英] Count number of values in a cell unless blank

问题描述

我有一个使用下拉列表的单元格，该列表允许进行多项选择.如何计算选择的数量?

I have a cell that uses a drop-down list that allows for multiple selections. How do I count the number of selections?

我用了这个: = LEN(A2)-LEN(SUBSTITUTE(A2，，"，"))+ 1

I used this: =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

但是，这并不代表空白或没有选择.如果没有选择，如何显示0?

But this doesn't account for blank or no selection. How can I display 0 as well if there is no selection?

推荐答案

如果选择的内容中没有空格，则用逗号替换空格，修剪结果，然后用逗号将空格计为上述值.

If your selections do not have spaces then substitute commas for spaces, trim the result then count for spaces as above with commas.

=LEN(trim(SUBSTITUTE(A2,","," ")))-LEN(SUBSTITUTE(trim(SUBSTITUTE(A2,","," "))," ",""))+sign(len(a2))

这篇关于计算单元格中的值数，除非为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！