Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reversing numbers to create dates | Excel Worksheet Functions | |||
Finding unique numbers in a column | Excel Discussion (Misc queries) | |||
Calculate numbers between 2 dates | Excel Discussion (Misc queries) | |||
Converting Text months to sortable Numbers or Dates | Excel Discussion (Misc queries) | |||
Dates to numbers | Excel Worksheet Functions |