 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2006Countif unique item with multiple criteria
Hi, How do I use the Count if or sumproduct formula to count cells with unique items. The database is organised as follows Product Year Segment Customer Amt A 2005 F XA A 2005 F XB A 2005 F XA A 2005 F XC A 2005 G XA A 2006 F B 2005 F B 2005 G B 2006 G B 2006 G Now if I want to count the number of Unique customers in Product A, for Year 2005, in Segment F, how is the countif or subproduct formula to be set up. By using the formula I should be able to get a count of 3 unique customers and not 4 for the above criteria. Appreciate if some one can help on it as it very important. Regards Sandip. =SUM(IF(FREQUENCY(IF((A2:A11<>"")* (B2:B11=2005)*(C2:C11="F"), MATCH(A2:A11,A2:A11,0)),ROW(INDIRECT("1:"&ROWS(A2:A11))))>0,1)) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) > Hi, > [quoted text clipped - 24 lines] > Regards > Sandip. Thanks Bob, The formula works fine now..... Thanks a lot. Regards Sandip. > =SUM(IF(FREQUENCY(IF((A2:A11<>"")* > (B2:B11=2005)*(C2:C11="F"), [quoted text clipped - 36 lines] > > Regards > > Sandip. Forgot to add it is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) > Hi, > [quoted text clipped - 24 lines] > Regards > Sandip. Hi Bob, I tried the formula and it does not seem to give the right total count. Though I don't fully understand the formula, it is not looking into Col D which has the list of customers to be counted. Only unique list count is required. Regards Sandip. > Forgot to add it is an array formula, it should be committed with > Ctrl-Shift-Enter, not just Enter. [quoted text clipped - 35 lines] > > Regards > > Sandip. Sorry Sandip, mis-read it. =SUM(IF(FREQUENCY(IF((D2:D11<>"")* (A2:A11="A")*(B2:B11=2005)*(C2:C11="F"), MATCH(D2:D11,D2:D11,0)),ROW(INDIRECT("1:"&ROWS(D2:D11))))>0,1)) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) > Hi Bob, > [quoted text clipped - 45 lines] > > > Regards > > > Sandip. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.