Countif, Frequency, Search...!

[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 is there a formula that would count unique instances of cells containing the word “Cheshire” and another formula to extract them please… In my example there are 4 cells containing the word “Cheshire” but one is duplicated so my count return needs to be 3.

Excel Workbook
	A	B	C	D
1	6th (Service) Battalion Yorkshire Regiment (Green Howards)		Unique	5
2	9th (Service) Battalion Yorkshire Regiment (Green Howards)
3	9th (Service) Battalion Cheshire Regiment		Cheshire	4
4	9th (Service) Battalion Cheshire Regiment
5	13th (Service) Battalion Cheshire Regiment
6	11th (Service) Battalion Cheshire Regiment
Sheet1

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

 

[Marcelo Branco]

To count unique instances of cells containing Cheshire (C3) try this array formula

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(C3,A1:A6)),MATCH(A1:A6,A1:A6,0)),ROW(A1:A6)-ROW(A1)+1),1))
Ctrl+Shift+Enter

M.

 

[circledchicken]

Hi,

Another option if your ok using helper columns might be something like this:

Book1
	A	B	C	D	E	F
1	List	Duplicate	Search result		Unique	5
2	6th (Service) Battalion Yorkshire Regiment (Green Howards)	FALSE	FALSE		Cheshire	3
3	9th (Service) Battalion Yorkshire Regiment (Green Howards)	FALSE	FALSE
4	9th (Service) Battalion Cheshire Regiment	FALSE	TRUE
5	9th (Service) Battalion Cheshire Regiment	TRUE	TRUE
6	13th (Service) Battalion Cheshire Regiment	FALSE	TRUE
7	11th (Service) Battalion Cheshire Regiment	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
B2	=ISNUMBER(MATCH(A2, A$1:A1, 0))
C2	=ISNUMBER(SEARCH(E$2, A2))
F1	=COUNTIF(B2:B7, FALSE)
F2	=COUNTIFS(B2:B7, FALSE, C2:C7, TRUE)

 

[How_Do_I]

Thanks Marcelo your formula worked... I couldn't use your formula circledchicken as I'm using xl 2003 but thanks for your time with your solution...

 

[circledchicken]

You're welcome.

You have the solution from Marcelo but just as an aside the 2003 version of the F2 formula in my example would be something like:

=SUMPRODUCT(--NOT(B2:B7), --C2:C7)