Countdiff with Criteria

[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]-->


Hello I want to count and then extract the non duplicates with a criteria… So with a criteria of “France” my F3 should return 2… Then HEBUTERNE MILITARY CEMETERY and DANTZIG ALLEY BRITISH CEMETERY, MAMETZ would be needed in F2 and F3 respectively… How would I achieve that please?


Sheet1

*	A	B	C	D	E	F
1	France	'4010'	HEBUTERNE MILITARY CEMETERY	2	France	3
2	France	'G/1330'	DANTZIG ALLEY BRITISH CEMETERY, MAMETZ	3	*	DANTZIG ALLEY BRITISH CEMETERY, MAMETZ
3	India	'176'	BOMBAY 1914-1918 MEMORIAL, MUMBAI	*	*	HEBUTERNE MILITARY CEMETERY
4	France	4060'	HEBUTERNE MILITARY CEMETERY	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:305px;"><col style="width:64px;"><col style="width:64px;"><col style="width:305px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D1 =COUNTDIFF($A$1:$A$4) F1 {=IF(A1:A3=E1,COUNTDIFF(C1:C4))} D2 =COUNTIF($A$1:$A$4,E1) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Rijnsent]

Hi how_do_i,

This article does more or less what you want: How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group

Cheers,

Koen