**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula to count the number of rows that contain at least one of two possible phrases, but not give me the total number of instances that both phrases occur overall.

Solved by E. B. in 33 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
18/06/2018 - 05:21

Hello

User
18/06/2018 - 05:22

Hi!

Excelchat Expert
18/06/2018 - 05:22

How are you?

Excelchat Expert
18/06/2018 - 05:23

Welcome to Got it Pro!

User
18/06/2018 - 05:23

Thanks! Each of these rows are responses from a user

Excelchat Expert
18/06/2018 - 05:23

Ok.

User
18/06/2018 - 05:24

And I'm hoping to easily calculate the number of unique individuals who answered at least 1 response: "Increase my bookings" or "Raise my nightly rate"

User
18/06/2018 - 05:24

So I *don't* want to double count the same person, if they answered both of those options (question was select all that apply)

User
18/06/2018 - 05:24

Does that make sense?

Excelchat Expert
18/06/2018 - 05:25

Yes, so to be clear

Excelchat Expert
18/06/2018 - 05:25

If a single user responds both "Increase my bookings" or "Raise my nightly rate" it should be counted as 1 rather than 2

User
18/06/2018 - 05:26

Yea, exactly

Excelchat Expert
18/06/2018 - 05:26

Ok

User
18/06/2018 - 05:26

(and they may have selected other things to too like "get more views on my listing", but I care only about bookings and rate

Excelchat Expert
18/06/2018 - 05:26

Ok.

Excelchat Expert
18/06/2018 - 05:26

I understand.

Excelchat Expert
18/06/2018 - 05:26

Give me few minutes to work on it.

User
18/06/2018 - 05:27

Ok, thank you! Would love to learn what formula you use in the end :)

Excelchat Expert
18/06/2018 - 05:27

Ok, Sure

Excelchat Expert
18/06/2018 - 05:35

Almost done, give me minute more

User
18/06/2018 - 05:37

Ok, thanks

Excelchat Expert
18/06/2018 - 05:41

Here it is

[Uploaded an Excel file]

Excelchat Expert
18/06/2018 - 05:41

Let me know, once you view the file. I will explain it further

User
18/06/2018 - 05:43

Ok, I'm looking at the file

Excelchat Expert
18/06/2018 - 05:44

Ok

User
18/06/2018 - 05:44

Thanks for making it so organized

Excelchat Expert
18/06/2018 - 05:44

My pleasure.

Excelchat Expert
18/06/2018 - 05:44

SO I have put the comments in Column A

Excelchat Expert
18/06/2018 - 05:44

Column B - I have used this formula - =ISNUMBER(SEARCH("Increase my bookings",A2))

Excelchat Expert
18/06/2018 - 05:45

so it will give me True and False for every cell if it contains the "Increase my bookings"

Excelchat Expert
18/06/2018 - 05:45

Column C - Will give me true and False for "Raise my nightly rate"

Excelchat Expert
18/06/2018 - 05:47

Column D - Will give me true and false for "Raise my nightly rate,Increase my bookings". As i checked the data is always listed with "Raise my nightly rate" first followed by "Increase my booking"

Excelchat Expert
18/06/2018 - 05:48

Then I calculated the total as the bottom in the row 249 using - =COUNTIF(B2:B248,"True") which only counts "true"

User
18/06/2018 - 05:48

That makes a lot of sense!

Excelchat Expert
18/06/2018 - 05:49

111+67+2x47=84

User
18/06/2018 - 05:49

Is there no way to do it just with COUNTIF?

User
18/06/2018 - 05:49

Like adding multiple potential phrases that it matches?

User
18/06/2018 - 05:49

Seems like this is pretty complicated, even though it gave me the answer :)

Excelchat Expert
18/06/2018 - 05:49

Let me think, give me a minute

User
18/06/2018 - 05:51

No worries, if it doesn't come to mind then maybe not

User
18/06/2018 - 05:51

Thank you so much for your help!

Excelchat Expert
18/06/2018 - 05:52

Actually I tried with Countif the problem is

Excelchat Expert
18/06/2018 - 05:52

Countif only counts the cell with full value so I am merging Count if with search.

Excelchat Expert
18/06/2018 - 05:52

Give me a minute

User
18/06/2018 - 05:52

It's okay. I have to run now. Thank you!

Excelchat Expert
18/06/2018 - 05:52

No problem

Excelchat Expert
18/06/2018 - 05:53

Just a correction

Excelchat Expert
18/06/2018 - 05:53

It would be 111+67-47 = 131

Excelchat Expert
18/06/2018 - 05:53

Here it is

[Uploaded an Excel file]

User
18/06/2018 - 05:53

Awesome

Excelchat Expert
18/06/2018 - 05:54

I hope, I answered your query to your satisfaction.

Excelchat Expert
18/06/2018 - 05:54

Please do provide a good feedback so that I continue helping others.

User
18/06/2018 - 05:54

Ok, I will!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*