COUNTIF Unique dates between ranges
I am trying to find a way COUNTIF(S)
between a specific set of dates, but I only get a count of unique dates. For context, I have been tracking counters over the course of several days. I can get more than one instance on the same day with different counter values. The fact that can happen is to score my meters a day.
Data!A3:A8700
is the date range.
I am summarizing things monthly, so in this case, for this example, I would like something that:
1) Uses COUNTIF
(or anything that will work!) Between date x and date y. I wish the X and Y were saved with Data!A3:A8700
. In this case, for example 11/1/2014 = x and 11/31/2014 = y
2) I would ONLY like to get the 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 example not 4.
I have found solutions to get unique numbers in the whole column, but nothing yet for the range within that column. Or rather, I was unable to figure out how to change them correctly.
source to share
As I understand it, you want the following, tell me if I'm wrong:
That is, starting from 01/11/2014 which is in D1 and ends on 12/11/2014 which is in D2 , you want to calculate unique dates. which fall between these two dates in your A1: A100 range .
This example has 6 unique dates as shown in result cell D4 . Here is the formula for counting unique values (dates) in a column containing a series of dates.
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 customize it according to your regional settings by replacing ";" on "," .
source to share