Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jeremy Ellison
 
Posts: n/a
Default Unique numbers if between dates

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Unique numbers if between dates

=SUM(--(FREQUENCY(IF((C1:C100=--"2005-01-01")*(C1:C100<=--"2005-01-31"),MAT
CH(A1:A100,A1:A100,0)),ROW(INDIRECT("1:"&ROWS(A1:A 100))))0))

as an aarry formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jeremy Ellison" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Unique numbers if between dates

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
reversing numbers to create dates Gilles Desjardins Excel Worksheet Functions 5 October 4th 05 11:33 PM
Finding unique numbers in a column coolkid397 Excel Discussion (Misc queries) 3 June 15th 05 07:53 AM
Calculate numbers between 2 dates Rajiv@Ivey Excel Discussion (Misc queries) 0 May 17th 05 06:25 PM
Converting Text months to sortable Numbers or Dates Greg Excel Discussion (Misc queries) 6 May 1st 05 03:32 AM
Dates to numbers John Excel Worksheet Functions 0 February 8th 05 12:45 PM


All times are GMT +1. The time now is 10:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"