Remember Me? December 9th 05, 09:32 AM posted to microsoft.public.excel.worksheet.functions
 Jeremy Ellison Posts: n/a 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 December 9th 05, 10:52 AM posted to microsoft.public.excel.worksheet.functions
 Bob Phillips Posts: n/a 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
news 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 December 9th 05, 11:06 AM posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a 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 December 9th 05, 11:11 AM posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Gilles Desjardins Excel Worksheet Functions 5 October 4th 05 11:33 PM coolkid397 Excel Discussion (Misc queries) 3 June 15th 05 07:53 AM [email protected] Excel Discussion (Misc queries) 0 May 17th 05 06:25 PM Greg Excel Discussion (Misc queries) 6 May 1st 05 03:32 AM John Excel Worksheet Functions 0 February 8th 05 12:45 PM

All times are GMT +1. The time now is 07:53 PM. Copyright ©2004-2019 ExcelBanter.