Countif formula in a paragraph

[vmohan1978]

I have text in merged cell a2:a4 (
Details of payment,q4 order summary report-completed
wt & dimension CA14-3345 - Completed
engraving : Acme - Progress
Tenon scienc & technologies - Completed
SBC @ 0.5% pay tax - Pending
Service tax 12.36% to 14% - Progress )

I required formula to count "completed" as "2"
similarly Progress as "2"
I am trying formula countif( a3,"Progress") it shows 1

 

[Asheesh]

Hi Mohan,

Share the sample file, it is difficult read the data above.

 

[vmohan1978]

find attached the excel file.

 

[Asheesh]

Hi Mohan, Put this in B7 of the attached file and drag it down

COUNT(SEARCH(A7,TRIM(MID(SUBSTITUTE($A$1," ",REPT(" ",LEN($A$1))),1+(ROW(INDIRECT("A1:A"&LEN($A$1)-LEN(SUBSTITUTE($A$1," ",""))+1))-1)*LEN($A$1),LEN($A$1)))))

Acknowledge with CTRL + SHIFT + ENTER

I don't want to sound stupid but I couldn't think of an easier way and there might be..I am brain dead right now I guess

 

[Asheesh]

Attached for ref

 

[Khalid NGO]

Hi Mohan,

I've found another option:

=(LEN($A$1)-LEN(SUBSTITUTE($A$1,A7,"")))/LEN(A7)

Regards,

 

[Asheesh]

Ignore the above - use the below

(LEN($A$1)-LEN(SUBSTITUTE($A$1,A7,"")))/LEN(A7)

and drag it down

 

[Asheesh]

@Khalid NGO bang on...how stupid was I on first attempt..

 

[Khalid NGO]

@Khalid NGO bang on...how stupid was I on first attempt..

haha, No dear, that was cool and good try, I was stretching my head to understand what you've done.
Try try & try, that's how we learn.

 

[vmohan1978]

Khalid & asheesh
It saves my lot of time, thanks lot for your support .

excel is ocean .... I need to learn lot....

 

[SUHAS KARHADKAR]

Hi Ashish,

In Mohan's example, you give the formula, but I don't understand what is $A$1,
I removed the '$', result is the same. Can your explain it? please?

Regards.
Suhas

 

[Asheesh]

@ Khalid NGO -

@SUHAS KARHADKAR go through the below link

http://www.excel-easy.com/functions/cell-references.html

 

[SUHAS KARHADKAR]

Hi, Khalid & Asheesh,
As I am a Excel User and I want to become a Power User, that is why I am asking this type of questions.

Thanx & regards.

Suhas

 

[Asheesh]

We all are learning here, so we can come up with any excel related question and an expert answers it for us.

 

[Khalid NGO]

Hi Suhas,

We all learn here, at one stage everyone are normal excel user, practice makes them perfect.

ROCKY BALBOA: "Every Champion was once a Contender that refused to Give Up"