Excel - COUNT Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc