Excel 2003-如何计算行中的日期，包括单元格中的多个日期 [英] Excel 2003 - How to count dates in row, including multiple dates in cell

问题描述

我有一些看起来像这样的数据:

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:

=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.

最后，我使用了这样的复合(?)countif公式:

In the end I went with a compound (?) countif formula like this:

=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!

推荐答案

好吧，由于没有其他建议，因此我将其列为答案.使用类似的东西:

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).

这篇关于Excel 2003-如何计算行中的日期，包括单元格中的多个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！