Frequency again...!

[How_Do_I]

<!--[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]--> I’m in a bit of a muddle here… On my worksheet I have 18781 burials in VARIOUS country locations… To count all the individual regiments of the burials (from the various countries), I used in cell AB2

=SUM(IF(FREQUENCY(IF(Regiment<>"",MATCH("~"&Regiment,Regiment&"",0)),ROW(Regiment)-ROW(I2)+1),1)) CSE

To pull the burials I used in cell AB3 downwards

=IF(ROWS($AB$3:AB3)>$AB$2,"",INDEX(Regiment,SMALL(IF(FREQUENCY(IF(Regiment<>"",MATCH("~"&Regiment,Regiment&"",0)),ROW(Regiment)-ROW($I$2)+1),ROW(Regiment)-ROW($I$2)+1),ROWS($AB$3:AB3)))) CSE…

When I count my returns I get 18781 so that is working…

Now I need only the burials in France so I’ve used in cell AE2

=SUM(IF(FREQUENCY(IF(Regiment<>"",IF(Country=$V$1,MATCH("~"&Regiment,Regiment&"",0))),ROW(Regiment)-ROW($I$2)+1),1)) CSE

V1 = “France” and in cell AE3 downwards I’ve used

=IF(ROWS($AE$3:AE3)>$AE$2,"",INDEX(Regiment,SMALL(IF(FREQUENCY(IF(Regiment<>"",IF(Country=$V$1,MATCH(Regiment,Regiment,0))),ROW(Regiment)-ROW($I$2)+1),ROW(Regiment)-ROW($I$2)+1),ROWS($AE$3:AE3)))) CSE to get the Regiments…

Can anyone see where I’m going wrong with the last two formulas as they are not returning the desired results…
<!--[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]--><!--[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:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; 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]-->

 

[How_Do_I]

I've solved this... Nothing wrong with the formulas, I counted my returns incorrectly...

 

[mole999]

glad you have a result, had you tried pivot tables ?