|
Unique sumproduct with criteria!
Hi,
I have a unique count using a sumproduct formula like this... =sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="") which seems to work ok. The problem is I then want to add another criteria saying only count uniques where the date in column F = "01/01/05". Nothing I've tried seems to work. Thanks in advance! |
Hi Naomi,
When you reference dates in formulas like this, the syntax is: F2:F100=DATEVALUE("5/5/2003") HTH "Naomi" wrote: Hi, I have a unique count using a sumproduct formula like this... =sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="") which seems to work ok. The problem is I then want to add another criteria saying only count uniques where the date in column F = "01/01/05". Nothing I've tried seems to work. Thanks in advance! |
D F
Client 1 Jan-04 Client 2 Feb-04 Client 1 Jan-04 Client 2 Jan-04 Client 4 Jan-04 I would hope for this to give me a value of 3 but instead I get a value of 2.5?! ie. Unique clients that also have Jan-04 in col F. Thanks. "Ken" wrote: It's hard for me to tell what's going on without seeing sample data. Perhaps you could list a sample of 9-10 rows from columns D and F and tell us your expected results? "Naomi" wrote: Thanks Ken, I've changed it to... =sumproduct((F2:F100=DATEVALUE("1/1/2004")/(countif(D2:D100,D2:D100)+(D2:D100="")) but I am getting a result that is a few short of what it should be. Any ideas? I'm going crazy here!! Thanks. "Ken" wrote: Hi Naomi, When you reference dates in formulas like this, the syntax is: F2:F100=DATEVALUE("5/5/2003") HTH "Naomi" wrote: Hi, I have a unique count using a sumproduct formula like this... =sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="") which seems to work ok. The problem is I then want to add another criteria saying only count uniques where the date in column F = "01/01/05". Nothing I've tried seems to work. Thanks in advance! |
Thanks Ken,
I've changed it to... =sumproduct((F2:F100=DATEVALUE("1/1/2004")/(countif(D2:D100,D2:D100)+(D2:D100="")) but I am getting a result that is a few short of what it should be. Any ideas? I'm going crazy here!! Thanks. "Ken" wrote: Hi Naomi, When you reference dates in formulas like this, the syntax is: F2:F100=DATEVALUE("5/5/2003") HTH "Naomi" wrote: Hi, I have a unique count using a sumproduct formula like this... =sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="") which seems to work ok. The problem is I then want to add another criteria saying only count uniques where the date in column F = "01/01/05". Nothing I've tried seems to work. Thanks in advance! |
It's hard for me to tell what's going on without seeing sample data. Perhaps
you could list a sample of 9-10 rows from columns D and F and tell us your expected results? "Naomi" wrote: Thanks Ken, I've changed it to... =sumproduct((F2:F100=DATEVALUE("1/1/2004")/(countif(D2:D100,D2:D100)+(D2:D100="")) but I am getting a result that is a few short of what it should be. Any ideas? I'm going crazy here!! Thanks. "Ken" wrote: Hi Naomi, When you reference dates in formulas like this, the syntax is: F2:F100=DATEVALUE("5/5/2003") HTH "Naomi" wrote: Hi, I have a unique count using a sumproduct formula like this... =sumproduct((D2:D100<")/(countif(D2:D100,D2:D100)+(D2:D100="") which seems to work ok. The problem is I then want to add another criteria saying only count uniques where the date in column F = "01/01/05". Nothing I've tried seems to work. Thanks in advance! |
Try the following...
=SUM(IF(FREQUENCY(IF((A1:A5<"")*(B1:B5="1/1/2004"+0),MATCH(A1:A5,A1:A5,0 )),ROW(INDIRECT("1:"&ROWS(A1:A5))))0,1,0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "Naomi" wrote: D F Client 1 Jan-04 Client 2 Feb-04 Client 1 Jan-04 Client 2 Jan-04 Client 4 Jan-04 I would hope for this to give me a value of 3 but instead I get a value of 2.5?! ie. Unique clients that also have Jan-04 in col F. Thanks. |
| All times are GMT +1. The time now is 02:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com