Find Text - Key Word or Phrase Between Dates

[mycon73]

Hi,

I'm looking to modify this or get another formula that can find text or more specifically, key word or phase.

Example - Not fully working for me

=SUMPRODUCT(--($A$2:$A$20>=$I$1),--($A$2:$A$20<=$J$1),--($B$2:$B$20="*Houston*"))

My date range is for the month of June, found in cells, $A$2:$A$20

In cells $B$2:$B$20, looking for the key word, "Houston". In the cells, I may have a suburb of Houston, such as Katy.

How can I modify or please provide a function that works.

Thanks

[Winon]

Hello Mycon73,

Have you tried:

=SUMPRODUCT(--($A$2:$A$20>=$I$1),--($A$2:$A$20<=$J$1),--($B$2:$B$20="*Houston*")+--($B$2:$B$20>"*Houston*"))

Regards.

[mycon73]

Hi Winon & Others

The previous or original formula was taken from another example. Here's my true formula from the worksheet that I'm working (I don't want to upload this particular file)

=SUMPRODUCT(--( '2014 Employment Opps'!$C$16:$C$1015>=H6),--( '2014 Employment Opps'!$C$16:$C$1015<=H7),--( '2014 Employment Opps'!$V$16:$V$1015="*Houston*")+--( '2014 Employment Opps'!$V$16:$V$1015>"*Houston*"))

I applied your suggestions, I got a large result, nearlly doubling my expected answer, which in this instance, I'm expecting around 58 - Which is the number of occurrences that "Houston" shows up between June 1st & June 31st.

What is the"+" doing here?

More importantly, what's a better formula to get intent?

Thanks

[Winon]

Hello Mycon73,

Thank you for the feedback.

It seems like you are outgrowing the limited capabilities of formulae, with yor increasing requirements.

If it is only Katy you want to include in the count of Houston, then this formula should do it, failing which, we may have to revert to vba, where you enter your search criteria via an InputBox, and take it from there.

=SUMPRODUCT(--('2014 Employment Opps'!$C$16:$C$1015>=H6),--('2014 Employment Opps'!$C$16:$C$1015<=H7),--('2014 Employment Opps'!$V$16:$V$1015="*Houston*")+--ISNUMBER(FIND("Katy",('2014 Employment Opps'!$V$16:$V$1015))))

The + will add Katy together with Houston.

Regards.

[mycon73]

Hi Winon,

OK - This function works great if I'm only seeing "Katy". However, as we know, every major metropolitan area has multiple suburbs, such as Houston does.

For instance, I have a cells that have: "Katy / Houston" or "Sugar Land / Houston".

Seeking modification of function that can match, look for these variations.

Thanks

[mycon73]

Hi Winon,

This seems to work well & exactly what I'm seeking:

=SUMPRODUCT(--('2014 Employment Opps'!$C$16:$C$1015>=H6),--('2014 Employment Opps'!$C$16:$C$1015<=H7),--ISNUMBER(FIND("Houston",('2014 Employment Opps'!$V$16:$V$1015))))

I took out the ' --('2014 Employment Opps'!$V$16:$V$1015="*Houston*")+ ' from your previous example.

Thanks

[Winon]

Hello mycon73,

Thank you for the feedback. We had a blackout for about 14 hours, and I could not get back to you sooner.

You did mention that;

For instance, I have a cells that have: "Katy / Houston" or "Sugar Land / Houston".

Seeking modification of function that can match, look for these variations.

That is why I have not suggested you amend your last formula as you did, because it would include "Houston" with any other variable associated with it.

Please try the attached sample Workbook, wherein it will allow you to search for specific variables only, as well.

Also, thank you for adding to my Reputation, it is much appreciated!

Regards.

[mycon73]

Hi Winon,

I didn't realize you put another example in. This works great too even though has different functionalilty that I was planning to use.

Thanks for the example

[mycon73]

All,

I have another question related to this.

If I want to find a key word or phrase from 2 different columns, how could I do this?

Let's say for example - Now ignoring date:


If I had
=SUMPRODUCT(--(ISNUMBER(FIND("Houston",('2014 Employment Opps'!$V$16:$V$1015))))

New column Z with $Z$16:$Z$1015 & find the word, "Open"

Thanks