Unique numbers if between dates
Forgot to coerce the second ending date for the first formula...
=SUM(IF(FREQUENCY(IF((C1:C4="1/1/2005"+0)*(C1:C4<="3/31/2005"+0),A1:A4),
IF((C1:C4="1/1/2005"+0)*(C1:C4<="3/31/2005"+0),A1:A4))0,1))
In article ,
Domenic wrote:
Try the following formulas that need to be confirmed with
CONTROL+SHIFT+ENTER...
=SUM(IF(FREQUENCY(IF((C1:C4="1/1/2005"+0)*(C1:C4<="3/31/2005"+0),A1:A4),
IF((C1:C4="1/1/2005"+0)*(C1:C4<="3/31/2005"),A1:A4))0,1))
or
=SUM(IF(FREQUENCY(IF((C1:C4=E1)*(C1:C4<=F1),A1:A4 ),IF((C1:C4=E1)*(C1:C4
<=F1),A1:A4))0,1))
...where E1 contains your start date, and F1 contains your end date.
Hope this helps!
In article ,
"Jeremy Ellison" wrote:
I have been using the following formula to add the number of unique numbers
in column A.
=SUM(IF(FREQUENCY(CaseData!A:A,CaseData!A:A)0,1))
HOWEVER,
Now I want to only count unique numbers in column A, only if column C is
between 1/1/2005 and 3/31/2005 (or any 2 dates for that matter).
EXAMPLE:
A C
5123456 1/1/2005
5123457 1/5/2005
5123457 1/5/2005
5123457 3/7/2009
The result should be 2 - because there are 2 unique numbers in A while C is
between 1/1/2005 adn 3/31/05.
|