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.