Help with excel formula...

[lucas6]

Hi All,

Please help...

Scenario: Basically I have a a spreadsheet with Raw data on 1 page, list of places, and a table/results sheet.
I currently manually go through the raw data and decide whether each line is proactive or not.
I would like a formula to do this for me, by searching a particular cell for 1 of 3 words (Proactive, Morning or Their Replication). If it finds 1 of the 3 words in the cell, it should count it as '1' and then move on to the next line.

So far I have tried:

=COUNTIFS(Data!H:H,$B2,Data!D:D,"*Proactive*")+COUNTIFS(Data!H:H,$B2,Data!D:D,"*Morning*")+COUNTIFS(Data!H:H,$B2,Data!D:D,"*Their Replication*")

The problem I am getting with this formula, is if a word appears twice in the cell, or 2 or more words appear in the cell, it will count the line more than once.

Is there a simple way I can amend this so that it will search for the 3 words and count only once if it finds 1.

I have also tried the following sumif formula, which works, but takes around 5-10 minutes to run the formula, so when any changes are made to the document it takes 5-10 minutes before I can do anything:

=SUMPRODUCT(-(Data!H:H=Table!$B2),-(MMULT(--ISNUMBER(SEARCH({"Proactive","Morning","Their Replication"},Data!D:D)),ROW($1:$3)/ROW($1:$3))>0))

Any help would be great! If any further info is required please let me know.
Thanks in advance

 

[Latchmaker]

Change the reference to A1 to the cell that needs searched and then place in a cell in same row as the cell that needs searched and fill down

=IF(IFERROR(FIND("Proactive",A1,1),0)=0,IF(IFERROR(FIND("Morning",A1,1),0)=0,IF(IFERROR(FIND("Their Replication",A1,1),0)=0,0,1),1),1)