count formula complicated

nostradamus

nostradamus

Board Regular
Joined
Aug 9, 2010
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi, I have a few questions - might get messy, but will try pointwise
Let us assume that there is no correlation bet. cities and states (lame ex. I know)

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cagrubh%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="City"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="State"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="place"></o:smarttagtype><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1073741899 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoNormalTable" style="width: 333pt; margin-left: 5.15pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="444"> <tbody><tr style="height: 12.75pt;"> <td style="width: 20pt; border: 1pt solid windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="27" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 52pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap">
A<o:p></o:p>​
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
B<o:p></o:p>​
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
C<o:p></o:p>​
</td> <td style="width: 56pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
D<o:p></o:p>​
</td> <td style="width: 56pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
E<o:p></o:p>​
</td> <td style="width: 53pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
F<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> State<o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> Cities<o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> Nos<o:p></o:p>
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap"> <o:p></o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
2<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Ohio</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">columbus</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
55<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
Count<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
3<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">New York</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">columbus</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
21<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
Dominance<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
4<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Texas</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">akron</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
32<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
Percent<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
5<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Idaho</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">athens</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
68<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
6<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Washington</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">bedford</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
24<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
7<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Montana</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
bexley<o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
15<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
8<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Oklahoma</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
bexley<o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
54<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
9<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Arkansas</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
bexley<o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
71<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
10<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Maine</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">bryan</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
18<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="27" nowrap="nowrap">
11<o:p></o:p>​
</td> <td style="width: 52pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Florida</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">
clyde<o:p></o:p>​
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="64" nowrap="nowrap">
19<o:p></o:p>​
</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 56pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 53pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 20pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="27" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 52pt; border-width: medium 1pt; border-style: none solid; border-color: -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="69" nowrap="nowrap">
Total<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0in 5.4pt; height: 12.75pt;" x:num="" x:fmla="=SUM(D3:D12)" valign="bottom" width="64" nowrap="nowrap">
377<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 56pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 56pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="75" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 53pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="71" nowrap="nowrap"> <o:p> </o:p>
</td> </tr> </tbody></table>
1. Count # of Cities under Col E2 (I know it's 7), but need formula
2. Count # of States under col F2 (from col A range)
3. under col E3 = city that occurs max no. of times under col B
4. under col F3 = total value from col C of the city that occurs max under col B
 
Sort by date Sort by votes
Matty, I copied the formulas you suggested and these are the answers I got, they are a bit different from yours. I copied the formulas you had posted on this Thread

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cagrubh%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="City"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="State"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="place"></o:smarttagtype><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:"Arial Unicode MS"; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:128; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1073741899 0 0 159 0;} @font-face {font-family:"\@Arial Unicode MS"; panose-1:2 11 6 4 2 2 2 2 2 4; mso-font-charset:128; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoNormalTable" style="width: 455.45pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="607"> <tbody><tr style="height: 12.75pt;"> <td style="width: 21.9pt; border: 1pt solid windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="29" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 62.3pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap">
A<o:p></o:p>​
</td> <td style="width: 52.4pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
B<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
C<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="80" nowrap="nowrap">
D<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
E<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
F<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
G<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> State<o:p></o:p>
</td> <td style="width: 52.4pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap"> Cities<o:p></o:p>
</td> <td style="width: 47.35pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap"> Nos<o:p></o:p>
</td> <td style="width: 59.9pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="80" nowrap="nowrap"> Occurances<o:p></o:p>
</td> <td style="width: 116.9pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap"> <o:p></o:p>
</td> <td style="width: 47.35pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
Cities<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
States<o:p></o:p>​
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
2<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Ohio</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="70" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">columbus</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
55<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="80" nowrap="nowrap">
2<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156" nowrap="nowrap"> Count<o:p></o:p>
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
1<o:p></o:p>​
</td> </tr> <tr style="height: 15pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
3<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">New York</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="70" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">columbus</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
21<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" x:str="" valign="bottom" width="80" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="156" nowrap="nowrap"> [FONT=&quot]Most Frequent<o:p></o:p>[/FONT]
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
3<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
4<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Texas</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">akron</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
32<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="80" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
Count of Most Frequent<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
5<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Idaho</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">athens</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
68<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="80" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
Dominance<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">athens</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
68<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
6<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Washington</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">bedford</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
24<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="80" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
7<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Montana</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
bexley<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
15<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="80" nowrap="nowrap">
3<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
8<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Oklahoma</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
bexley<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
54<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:str="" valign="bottom" width="80" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
9<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Arkansas</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
bexley<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
71<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:str="" valign="bottom" width="80" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
10<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Maine</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
<st1:city w:st="on"><st1:place w:st="on">bryan</st1:place></st1:city><o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
18<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="80" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% silver; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="29" nowrap="nowrap">
11<o:p></o:p>​
</td> <td style="width: 62.3pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap"> <st1:state w:st="on"><st1:place w:st="on">Florida</st1:place></st1:state><o:p></o:p>
</td> <td style="width: 52.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="70" nowrap="nowrap">
clyde<o:p></o:p>​
</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="63" nowrap="nowrap">
19<o:p></o:p>​
</td> <td style="width: 59.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="80" nowrap="nowrap">
1<o:p></o:p>​
</td> <td style="width: 116.9pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> <td style="width: 47.35pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p></o:p>​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 21.9pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="29" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 62.3pt; border-width: medium 1pt; border-style: none solid; border-color: -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="83" nowrap="nowrap">
Total<o:p></o:p>​
</td> <td style="width: 52.4pt; padding: 0in 5.4pt; height: 12.75pt;" x:num="" valign="bottom" width="70" nowrap="nowrap">
377<o:p></o:p>​
</td> <td style="width: 47.35pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 59.9pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="80" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 116.9pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="156" nowrap="nowrap"> <o:p> </o:p>
</td> <td style="width: 47.35pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p> </o:p>​

</td> <td style="width: 47.35pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="63" nowrap="nowrap">
<o:p> </o:p>​

</td> </tr> </tbody></table>
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi,

Did you commit the 'array' ones with Ctrl+Shift+Enter? I suspect this to be the issue.

Actually, if you have no blank (empty) cells in your data, we could simplify the formulas in F2 and G2:

For F2, either:

Code: 
=SUM(IF(FREQUENCY(MATCH(B2:B11,B2:B11,0),ROW(B2:B11)-ROW(B2)+1),1))

Which needs committing with Ctrl+Shift+Enter, or:

Code: 
=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))

Which can just be entered normally.

For G2, either:

Code: 
=SUM(IF(FREQUENCY(MATCH(A2:A11,A2:A11,0),ROW(A2:A11)-ROW(A2)+1),1))

Which needs committing with Ctrl+Shift+Enter, or:

Code: 
=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

Which can just be entered normally.

Hope this helps.

Matty
 
Upvote 0
thanks, the CSE and regular formulas work great. But I'm still having problems under col F5 where you got Bexley and I have Athens
 
Upvote 0
nevermind, I tried the CSE for all the formulas and it worked great this time.
Thanks a bunch. :)
 
Upvote 0
ok Matty,
In the formulas you provided for col F2, G2 - it works based on information in cols A, B. Now, I will be entering this data in cols A, B; some times I'd have unpto A15 and may be upto A250 (same thing for col B). It could vary with different data. Is there a way for the formulas under F and G to take care of this. Currently if I extend the range of the below formula to say B2:B1000, and there are actually no values upto row 1000, then it returns error.

=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))</pre>
 
Upvote 0
If you use the formulas in post #9, you should be OK to extend the ranges without seeing odd results.

Either that or you use dynamic named ranges for your data, which will shrink/grow as your data does. For more on this subject, see:

http://www.contextures.com/xlNames01.html#Dynamic

Post back if anything's unclear...

Matty
 
Upvote 0
Matty, the Dynamic Range seems the way to go. But in simpler formulas I has seen A2:A used. Do you think this would work in this case? I am doing some reading on Dynamic Range meanwhile.
Thanks

This is in reference to your advice on Post# 9
 
Last edited:
Upvote 0
But in simpler formulas I has seen A2:A used.
Click to expand...

Not sure what you mean...

In the scenario you describe, I would opt for a dynamic named range everytime.

Did the link help you to understand what they're about?

Matty
 
Upvote 0
yes, the link did help me get started. I'll have to look up more help sessions online reg. that though. I might bother you for some help reg that at some point. :)
I had meant for instance =SUM(A:A) = it would sum all the values in col A; instead of a specific range of =SUM(A1:A1000), or for that matter any formula using that type of range.
 
Upvote 0
I would never encourage the use of entire Columns in formulas (some funtions can't actually handle them); always best to use a dynamic range in my opinion.

If you need any help setting them up, let me know. Though the link I posted explains matters quite simply. :)

Matty
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
854
gvsumich
G
E
  • Question
Adding a custom message.
Replies
1
Views
450
Micron
M
E
  • Question
VBA for Web Scraping help
Replies
0
Views
608
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,639
Messages
6,125,967
Members
449,276
Latest member
surendra75

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top