counting diffeneces in range
Is there a function which will count in a range of cells (text, i.e. state abbreviations) each time a new state abbreviation occurs? Thanks for any help with this, going crazy here... -- mallets123 ------------------------------------------------------------------------ mallets123's Profile: http://www.excelforum.com/member.php...o&userid=25090 View this thread: http://www.excelforum.com/showthread...hreadid=386023 |
You want Unique records, yes?
You can try this: http://www.officearticles.com/excel/...soft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "mallets123" wrote in message ... Is there a function which will count in a range of cells (text, i.e. state abbreviations) each time a new state abbreviation occurs? Thanks for any help with this, going crazy here... -- mallets123 ------------------------------------------------------------------------ mallets123's Profile: http://www.excelforum.com/member.php...o&userid=25090 View this thread: http://www.excelforum.com/showthread...hreadid=386023 |
Some options...
[A] =SUMPRODUCT((Range<"")/COUNTIF(Range,Range&"")) [b] =SUM(IF(Range<"",1/(COUNTIF(Range,Range))) which must be confirmed with control+shift+enter. [C] =COUNTDIFF(Range,,"") which requires Longre's morefunc.xll add-in. mallets123 wrote: Is there a function which will count in a range of cells (text, i.e. state abbreviations) each time a new state abbreviation occurs? Thanks for any help with this, going crazy here... -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Thanks for all of your help, I've got it to work like I want it, however I now need it to list these state abbreviations that are different in a series of cells. Basically my example is I have a groups that are traveling to different states. I've got excel counting how many different states were visited. Now I need excel to list them in a series of cells so I can count the total number of different states visited between several groups. Whoah.... I'm using =SUM(IF(FREQUENCY(IF(LEN(F5:F8)0,MATCH(F5:F8,F5:F 8,0),""),IF(LEN(F5:F8)0,MATCH(F5:F8,F5:F8,0),"")) 0,1)) this counts the different states incountered. If two groups are traveling in different time frames but I need to keep a cumulative total of different states visited, isn't it correct to list the states seperately somewhere where excel can count the total of both groups? Please help... -- mallets123 ------------------------------------------------------------------------ mallets123's Profile: http://www.excelforum.com/member.php...o&userid=25090 View this thread: http://www.excelforum.com/showthread...hreadid=386023 |
All times are GMT +1. The time now is 07:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com