• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match cell contents to a list

M

Merovingian

New Member
Good Morning all, I am novice with Excel and trying out new formulas and hoping to learn allot. Please see attached workbook I want to create using an if function.
My aim will be the following :
in tab "Form Response1" , responses will be loaded in col B2, I would like to count the number of times a specific word/phrase appears in B1 matching the "Header" from the "data" tab A2:A10 or B2:10 or C2:C10
I was using =IF(ISNUMBER(SEARCH(Data!$A:$A,$B2)), "Yes", "No") with Yes as the result if the word in cell B2 contains any of the words in dataA:A, but it only looks at the fist cell (A2 ) and not the whole column A

Please see attached and thank you in advance.
M

[Edit by Hui, Post shifted and renamed.]
 

Attachments

Last edited by a moderator:
First of all welcome to the forum..

Check this...

In C2 - SUM(IFERROR(IF(ISNUMBER(SEARCH(Data!$A$2:$A$11,$B2)),1,),""))
In D2 - SUM(IFERROR(IF(ISNUMBER(SEARCH(Data!$B$2:$B$11,$B2)),1,),""))
In E2 - SUM(IFERROR(IF(ISNUMBER(SEARCH(Data!$C$2:$C$11,$B2)),1,),""))

Note: These are to be array entered..


EDIT :

Removed iferror..

In C2 - SUM(IF(ISNUMBER(SEARCH(Data!$A$2:$A$11,$B2)),1,))
In D2 - SUM(IF(ISNUMBER(SEARCH(Data!$B$2:$B$11,$B2)),1,))
In E2 -SUM(IF(ISNUMBER(SEARCH(Data!$C$2:$C$11,$B2)),1,))

Again Array enter
 
Last edited:
Thank you Asheesh, Much Appreciated,

I checked the above and both work to a point, is there a way the formula can count how many of the words from col A:A on the data sheet are appearing in B2.

With the above formula I get a result of 1, but this is inconsistent.
e.g : should be as per below.
Time Results Header 1 Header 2 Header 3
09:50 Symptom1,Symptom2,Symptom21,
Symptom3,Symptom4 4 2 1

Regards
M
 
You must log in or register to reply here.
Back
Top