COUNTIF范围之间的唯一日期 [英] COUNTIF Unique Dates Between a Range

问题描述

我正在尝试找到一种在某一组日期之间 COUNTIF（S）的方式，但只能获取唯一日期的计数。对于上下文，我在几天内跟踪米。我可以在同一天获得多个不同的仪表值的实例。可能发生的事实是拧紧我的米/天平均值。

数据！A3：A8700 - 是日期范围。

我每月总结一些事情，所以在这种情况下，对于这个例子，我想要一些：

1）在日期x和日期y之间使用 COUNTIF （或任何可以工作的内容）！希望X和Y存储从 DATA！A3：A8700 中被拉出。在这种情况下，例如，11/1/2014 = x和11/31/2014 = y

2）我只想获得唯一的日期作为有效的计数的一部分：

  11/1/2014 
 11/2/2014 
 11/2 / 2014 
 11/3/2014 
  

将返回3，例如不是4。

我已经找到解决方案来获取整个列中的唯一数字，但是该列中的范围还没有。或者，更准确地说，我无法弄清楚如何正确修改那些。

解决方案

据我所知，你想要以下内容，告诉我是不是我错了：






即从 D1 开始 01/11/2014 >并以 D2 中的 12/11/2014 结尾，您要计算属于这些两个日期之间的唯一日期 A1：A100 范围内的。

在此示例中，有6个唯一日期，如结果单元格 D4 所示。
这里是计算包含一系列日期的列中唯一值（日期）的公式。

公式 ：

$（$ A $ 1 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ D $ 2; IF（$ A $ 1：$ A $ 97<> 中; MATCH（$ A $ 1：$ A $ 97; $ A $ 1：$ A $ 97; 0））））; ROW（$ A $ 1：$ A $ 97）-ROW（$ A $ 1）+1）; 1））

< br>
，您可能需要根据区域设置进行调整，将替换为，。

这里是 从保管箱下载的示例excel表。

I am trying to find a way to COUNTIF(S) between a certain set of dates, but only get the count of the unique dates. For context, I am tracking meters over days. I can get more than one instance on the same day with differing meter values. The fact that can happen is screwing up my meters/day average.

Data!A3:A8700 - is the date range.

I am summarizing things monthly, so, in this case, for this example, I'd like something that:

1) Uses COUNTIF (or anything that will work!) between date x and date y. Would like X and Y stored to be pulled from DATA!A3:A8700. In this case, for instance, 11/1/2014 = x and 11/31/2014 = y

2) I would ONLY like to get unique dates as a valid part of the count:

11/1/2014
11/2/2014
11/2/2014
11/3/2014

Would return 3, for instance, not 4.

I have found solutions to get unique numbers in the entire column, but nothing yet for a range within that column. Or, more accurately, I have not been able to figure out how to properly modify those.

解决方案

As far as I understand, you want the following, tell me if I am wrong:



That is, starting from 01/11/2014 that is in D1 and ending in 12/11/2014 that is in D2, you want to calculate unique dates that fall inbetween these two dates in your A1:A100 range.

And in this example, there are 6 unique dates, as shown in result cell D4. Here is the formula to count unique values (dates) in column containing a series of dates.

the formula:

=SUM(IF(FREQUENCY(IF($A$1:$A$97>=D$1;IF($A$1:$A$97<=D$2;IF($A$1:$A$97<>"";MATCH($A$1:$A$97;$A$1:$A$97;0))));ROW($A$1:$A$97)-ROW($A$1)+1);1))

you may need to adjust it according to your regional settings, by replacing ";" with ",".

here is the example excel sheet to download from dropbox.

这篇关于COUNTIF范围之间的唯一日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！