COUNTIFS without duplicates

[Gr33ned]

I am trying to count the number of dates in a range, counting duplicates only once.

My attempt:

=COUNTIFS(A:A, ">=" & B1, A:A, "<=" & C1)

In this case, my dates are in column A, and I want to count it only if it is between the dates listed in B1 and C1. This is working, except it is counting duplicates.

e.g. B1 = June 30, C1 = July 4
Column A:
July 1
July 1
July 2
July 3

provides a count of 4, when I would like 3.

 

[jorismoerings]

Hi,

Give this a try...

=SUMPRODUCT((A1:A100 <> " ")/COUNTIF(A1:A100,A1:A100 & ""))

 

[Gr33ned]

Hi,

Give this a try...

=SUMPRODUCT((A1:A100 <> " ")/COUNTIF(A1:A100,A1:A100 & ""))

Thanks
Tried this but still gave count of 4..

 

[Chrisdontm]

I tried the solution, and when I took the space away from the first double quotes then I got 3...

 

[jorismoerings]

Apologies , there wasn't suppose to be a space in the formula.

 

[Aladin Akyurek]

If column A consists of true dates, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A10>=B1,IF(A2:A10<=C1,A2:A10)),A2:A10),1))