I have some data that looks like this:
我有一些这样的数据:
However, I would like the total to exclude any text entries (the formula should count dates only). Because some boxes can have multiple dates in, I appreciate that those boxes will be counted as text. Currently I am using this:
但是，我希望total排除任何文本条目(公式只计算日期)。因为有些盒子可以有多个日期，我很感激这些盒子会被算作文本。目前我正在使用:
=SUMPRODUCT(--(B2:D2<>""),LEN(B2:D2)-LEN(SUBSTITUTE(B2:D2,",",""))+1)
Which counts the multiple dates in cells perfectly, but is also counting the text entries. Thinking about it I could ask it to exclude instances which match 'Holiday' or 'Left'etc, but any pointers would be appreciated.
它可以完美地计算单元中的多个日期，但也可以计算文本条目。考虑到这一点，我可以要求它排除与“Holiday”或“Left”等匹配的实例，但是任何指针都是值得赞赏的。
EDIT: In the end I went with a compound (?) countif formula like this:
编辑:最后我使用了一个复合(?)countif公式:
=COUNTIF(B3:D3,"Holiday")+COUNTIF(B3:D3,"Left")
and minused the total from the total yielded from the sumproduct. Not an elegant solution, but it does work!
将总和的总产出减去。这不是一个优雅的解决方案，但它确实有效!
2 个解决方案
#1
0
Well, as there's been no other suggestions, I will list this as the answer I went with. Using something like:
由于没有其他的建议，我将把这个列为我的答案。使用类似:
=COUNTIF(B3:D3,"Holiday")+COUNTIF(B3:D3,"Left")
will help identify any specific entries I don't want to count in my total. I can then use that to minus from the total achieved with the original formula. Because my text entries come from a data validation list, this is a usable solution, but it's not ideal if you were looking to exclude any text entries (apart from multiple dates).
将有助于识别任何特定的条目，我不想在我的总数中计数。然后我可以用这个来减去用原公式得到的总数。因为我的文本条目来自一个数据验证列表，所以这是一个可用的解决方案，但是如果您希望排除任何文本条目(除了多个日期)，这并不理想。
#2
0
did you try using COUNTIF with wildcards. Since your dates always contain a "/", you can try -
你试过用复数形式来表示通配符吗?既然你的约会总是包含一个"/"，你可以试试-
COUNTIF(A1:D1,"*/*")
#1
0
Well, as there's been no other suggestions, I will list this as the answer I went with. Using something like:
由于没有其他的建议，我将把这个列为我的答案。使用类似:
=COUNTIF(B3:D3,"Holiday")+COUNTIF(B3:D3,"Left")
will help identify any specific entries I don't want to count in my total. I can then use that to minus from the total achieved with the original formula. Because my text entries come from a data validation list, this is a usable solution, but it's not ideal if you were looking to exclude any text entries (apart from multiple dates).
将有助于识别任何特定的条目，我不想在我的总数中计数。然后我可以用这个来减去用原公式得到的总数。因为我的文本条目来自一个数据验证列表，所以这是一个可用的解决方案，但是如果您希望排除任何文本条目(除了多个日期)，这并不理想。
#2
0
did you try using COUNTIF with wildcards. Since your dates always contain a "/", you can try -
你试过用复数形式来表示通配符吗?既然你的约会总是包含一个"/"，你可以试试-
COUNTIF(A1:D1,"*/*")