Count and Extract

[How_Do_I]

In the following how would I count the cells that contain "Child Folder" and then extract that cell less the words "Child Folder" please?

The returns I'm after are in cells C1:C4.

Excel Workbook
	A	B	C
1	Child Folder: Cycling		3
2	Elham Valley Cycle Ride.pdf		Cycling
3	Giro 2014.pdf		Walks
4	Miner's Way Cycle Map.pdf		War
5	Child Folder: Walks
6	Frontline Britain Trail Map.pdf
7
8	Child Folder: War
9	Bombing London - Unexploded Bombs.pdf
10	fortitude.pdf
Sheet1

 

[How_Do_I]

Actually I've got my C2 downwards:

CSE: =TRIM(SUBSTITUTE(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER(SEARCH("Child Folder",$A$1:$A$10)),ROW($A$1:$A$10)-ROW($A$1)+1),ROW(A1))),"Child Folder: ",""))

It's the count in C1 that I can't work out.

 

[uacdub]

If you're just wanting a count this will work.

=COUNTIF(A:A,"*Child Folder*")

 

[jtakw]

Nevermind, already answered.

 

[How_Do_I]

Thank you both.

 

[Aladin Akyurek]

@ How_Do_I

Insert a row or two before the first row. Are you still happy with the results you get?

 

[How_Do_I]

Hi Aladin, I used the ROW(A1) as the k in SMALL purely out of laziness in a test workbook, I will be using the robust ROWS($A$2:A2) or something similar in my proper workbook.

 

[How_Do_I]

That said, in the Jeanie all my example finish with a ".pdf" file extension so I used:

CSE =IF(ROWS($K$2:K2)>$I$2,"",INDEX($A$1:$A$10,SMALL(IF(ISNUMBER(SEARCH(".pdf",$A$1:$A$10)),ROW($A$1:$A$10)-ROW($A$1)+1),ROWS($K$2:K2)))) to extract everything without "Child Folder". What could I use to extract when I have multiple file extensions? Will I need another IF(ISNUMBER(SEARCH(".jpeg",range) in my formula?

 

[Aladin Akyurek]

@ How_Do_I

In I2 just enter:

=SUM(COUNTIFS($A$1:$A$11,"*"&{".pdf",".jpeg"}&"*"))

In K2 control+shift+enter, not just enter, and coy down:

=IF(ROWS($K$2:K2)>$I$2,"",INDEX($A$1:$A$11,SMALL(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE({".pdf";".jpeg"}),$A$1:$A$11))+0,{1;1}),ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($K$2:K2))))

 

[How_Do_I]

Thank you Aladin...