|
Advanced unique cell count with multiple conditions ... help!
I have spent 2 days now trying to get this to work, so any help would be much appreciated. :) I am trying to count the number of unique cells in a range, where the unique cells have a corresponding date that must fall within a particular period. eg, consider the following data: (csv format) #ColA,#ColB Jane Smith,17-Dec-03 Luke Simons,21-Jun-04 Nick James,16-Dec-03 Bob Sampson,3-Jul-03 Greg Thingh,28-Nov-03 Kieran Smyth,23-Sep-04 Michael Smith,27-Aug-04 Michael Smith,30-Jul-04 Jane Sheppard,4-Aug-04 Borris Lee,19-Jul-05 Simon Johns,6-Sep-05 Alex Foote,12-Sep-05 Chris Hander,21-Sep-05 Jane Sheppard,24-Sep-05 Georgia Se,24-Sep-05 If I count all unique ColA cells that have a ColB date between 1-Sep-05 and 30-Sep-05 inclusive, I get 5. But how do I do this using excel worksheet formulas?? I have tried the following array formula (enter with CTRL+SHIFT+ENTER): =SUM(IF(FREQUENCY(A2:A16,A2:A16)0,IF(B2:B16=DATE (2005,1,1),IF(B2:B16<=DATE(2005,9,30),1)))) The problem is, the above formula equates to 4. :mad: I think what is happening is the frequency filter returns a range of cells that are the first unique occurences. That is, just the unique filter on ColA returns 12, BUT the first occurence of Jane Sheppard is returned. This cell (A10 in this example) has a value 4-Aug-04 in B10 which does not satisfy the date conditions, hence the final sum is 4 and not 5. Please please, if someone could help me to write a worksheet that will count unique cells with conditions such as in my example, I would be ever so thankful!!! (no vba scripts please, worksheet formulas only) -- Flystar ------------------------------------------------------------------------ Flystar's Profile: http://www.excelforum.com/member.php...fo&userid=9275 View this thread: http://www.excelforum.com/showthread...hreadid=470433 |
Let A2:B16 house the sample you provided. Some options... If you have Longre's morefunc.xll add-in... =COUNTDIFF(IF($B$2:$B$16-DAY($B$2:$B$16)+1=D2,$A$2:$A$16,0),FALSE,0) which must be confirmed with control+shift+enter. Otherwise... Either: =SUMPRODUCT(--($A$2:$A$16<""),--($B$2:$B$16-DAY($B$2:$B$16)+1=D2),--(MATCH($A$2:$A$16&$B$2:$B$16,$A$2:$A$16&$B$2:$B$16 ,0)=ROW($B$2:$B$16)-ROW($B$2)+1)) Or: =SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<""),MATCH($A$2: $A$16,$A$2:$A$16,0)),ROW($B$2:$B$16)-ROW($B$2)+1)0,1)) Flystar wrote: I have spent 2 days now trying to get this to work, so any help would be much appreciated. :) I am trying to count the number of unique cells in a range, where the unique cells have a corresponding date that must fall within a particular period. eg, consider the following data: (csv format) #ColA,#ColB Jane Smith,17-Dec-03 Luke Simons,21-Jun-04 Nick James,16-Dec-03 Bob Sampson,3-Jul-03 Greg Thingh,28-Nov-03 Kieran Smyth,23-Sep-04 Michael Smith,27-Aug-04 Michael Smith,30-Jul-04 Jane Sheppard,4-Aug-04 Borris Lee,19-Jul-05 Simon Johns,6-Sep-05 Alex Foote,12-Sep-05 Chris Hander,21-Sep-05 Jane Sheppard,24-Sep-05 Georgia Se,24-Sep-05 If I count all unique ColA cells that have a ColB date between 1-Sep-05 and 30-Sep-05 inclusive, I get 5. But how do I do this using excel worksheet formulas?? I have tried the following array formula (enter with CTRL+SHIFT+ENTER): =SUM(IF(FREQUENCY(A2:A16,A2:A16)0,IF(B2:B16=DATE (2005,1,1),IF(B2:B16<=DATE(2005,9,30),1)))) The problem is, the above formula equates to 4. :mad: I think what is happening is the frequency filter returns a range of cells that are the first unique occurences. That is, just the unique filter on ColA returns 12, BUT the first occurence of Jane Sheppard is returned. This cell (A10 in this example) has a value 4-Aug-04 in B10 which does not satisfy the date conditions, hence the final sum is 4 and not 5. Please please, if someone could help me to write a worksheet that will count unique cells with conditions such as in my example, I would be ever so thankful!!! (no vba scripts please, worksheet formulas only) |
BTW, D2 houses a first day date like 1-Sep-05.
Aladin Akyurek wrote: Let A2:B16 house the sample you provided. Some options... If you have Longre's morefunc.xll add-in... =COUNTDIFF(IF($B$2:$B$16-DAY($B$2:$B$16)+1=D2,$A$2:$A$16,0),FALSE,0) which must be confirmed with control+shift+enter. Otherwise... Either: =SUMPRODUCT(--($A$2:$A$16<""),--($B$2:$B$16-DAY($B$2:$B$16)+1=D2),--(MATCH($A$2:$A$16&$B$2:$B$16,$A$2:$A$16&$B$2:$B$16 ,0)=ROW($B$2:$B$16)-ROW($B$2)+1)) Or: =SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<""),MATCH($A$2: $A$16,$A$2:$A$16,0)),ROW($B$2:$B$16)-ROW($B$2)+1)0,1)) [...] |
Thanks Aladin, your options work wonderfully. For the benefit of others, the final formula I chose to use was: =SUM(IF(FREQUENCY(IF(($B$2:$B$16-DAY($B$2:$B$16)+1=D2)*($A$2:$A$16<""),MATCH($A$2: $A$16,$A$2:$A$16,0)),ROW($B$2:$B$16))0,1)) -- Flystar ------------------------------------------------------------------------ Flystar's Profile: http://www.excelforum.com/member.php...fo&userid=9275 View this thread: http://www.excelforum.com/showthread...hreadid=470433 |
| All times are GMT +1. The time now is 10:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com