Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I am attempting to create a function which generates a particular number value if a letter is found within a string of words. If that letter is found more than once than I want the numbers to be summed
Solved by D. A. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/09/2018 - 10:39
Welcome to ExcelChat!
Excelchat Expert 19/09/2018 - 10:39
I see that your question is about finding a letter and generating the result based on it
Excelchat Expert 19/09/2018 - 10:39
I will help you with the problem and explain the solution afterwards.
Excelchat Expert 19/09/2018 - 10:39
Before we get started, this is a reminder that our policy is one problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 19/09/2018 - 10:39
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User 19/09/2018 - 10:39
Sounds good
Excelchat Expert 19/09/2018 - 10:39
Could you provide some data sample or file based on which we need to make a solution?
User 19/09/2018 - 10:43
I am attempting to do that now.
Excelchat Expert 19/09/2018 - 10:43
I believe you can just type some text in A1, and explain which result do you want to get in B1 and why. In general I can quickly solve the problem that you described, but I need some more details
Excelchat Expert 19/09/2018 - 10:45
I would like to know which letter is required to be found, which result is expected to be returned. You also mentioned that if a letter was found more than once, then numbers have to be summed. I would like to understand which numbers do we need to sum in this case.
Excelchat Expert 19/09/2018 - 10:45
So just small sample of input and expected output and few explanations will be enough to cover all of these questions
User 19/09/2018 - 10:46
Let me send you a picture really quick to provide context
[Uploaded an Excel file]
Excelchat Expert 19/09/2018 - 10:47
Thank you, I copied the data from this sheet to the sheet near the chat. By the way, can you see the sheet near the chat?
User 19/09/2018 - 10:49
I can not. Here is a picture
User 19/09/2018 - 10:49
Were you able to get that?
[Uploaded an Excel file]
Excelchat Expert 19/09/2018 - 10:49
Yes, just appeared
Excelchat Expert 19/09/2018 - 10:50
Okay, I see the picture, but I don't understand it.. could you add some explanations?
Excelchat Expert 19/09/2018 - 10:51
You are trying to figure out how many times each letter is found in each of these boxes, right?
User 19/09/2018 - 10:51
Certainly Could. Do you see how the Y in blue is in the center column of the larger boxes and the center column of the letters within its box.
Excelchat Expert 19/09/2018 - 10:52
Yes, I can
User 19/09/2018 - 10:52
I was hoping that if a typed a word like yound I could get this to produce the number two in a column entitled middle column
User 19/09/2018 - 10:53
Moreover if there were two Ys I was hoping that it would produce the number 4
User 19/09/2018 - 10:53
Excelchat Expert 19/09/2018 - 10:54
When we talk about the middle column, do we include both yellow and blue boxes?
User 19/09/2018 - 10:54
Yes we do
Excelchat Expert 19/09/2018 - 10:54
In this case for the word young, the result would be 3
Excelchat Expert 19/09/2018 - 10:54
User 19/09/2018 - 10:55
My apologies I mean to say that the letter Y would produce the number two as it is nested within two middle columns. Does that make sense?
Excelchat Expert 19/09/2018 - 10:56
When you see the pop up, please extend the session
Excelchat Expert 19/09/2018 - 10:57
Well, I don't really understand it, since Y belongs only to one middle box, and can be found there only once..
User 19/09/2018 - 10:59
In the actual Program a participant is trying to type. Upon picking the blue box a similar configuration appears with the constituent letters of the blue box arranged as they are seen within the blue box. The participant must then select the center column a second time in order to pick the Y totalling two choices of the center column.
User 19/09/2018 - 11:01
I understand that this is a rather fuzzy description but I would be satisfied with knowing how out put a number for each occurence of a letter within a word generally
Excelchat Expert 19/09/2018 - 11:02
I can see in total 6 boxes with letters inside it. Inside these boxes, I can't see any other boxes.. do I have the right picture or I am missing something?
Excelchat Expert 19/09/2018 - 11:02
Okay, I will try to help you with more general question then which you asked
Excelchat Expert 19/09/2018 - 11:02
Because I am sure that we won't able to solve this quiz for the session time
User 19/09/2018 - 11:03
haha sounds good
Excelchat Expert 19/09/2018 - 11:04
Did I understand properly, that for example in the word apple, we need to know how many times we have the letter p inside it, and the result will be 2?
User 19/09/2018 - 11:06
I am looking to be able to assign a number to a letter rather than just count its occurences.
User 19/09/2018 - 11:07
For instance If a word contains Y I want that to count as 2
Excelchat Expert 19/09/2018 - 11:07
You can add some logic for that
Excelchat Expert 19/09/2018 - 11:07
For example, we count how many times we have Y and then multiply it by
Excelchat Expert 19/09/2018 - 11:07
by 2
User 19/09/2018 - 11:08
sounds Good
Excelchat Expert 19/09/2018 - 11:08
Okay, I will make for you an example for this case
Excelchat Expert 19/09/2018 - 11:09
I think you will understand how to use it and will able to create a complicated solution for your quiz
Excelchat Expert 19/09/2018 - 11:09
So we have word YOUNG in A1
Excelchat Expert 19/09/2018 - 11:09
In B1 we put the following formula
User 19/09/2018 - 11:10
Unfortunately the display is not showing up on my end
Excelchat Expert 19/09/2018 - 11:10
I will send the formula in a while to the chat
Excelchat Expert 19/09/2018 - 11:11
Excelchat Expert 19/09/2018 - 11:11
So how it works
Excelchat Expert 19/09/2018 - 11:11
1. We find the length of the word
Excelchat Expert 19/09/2018 - 11:11
2. We find the length of the word, if we replace each Y with nothing
Excelchat Expert 19/09/2018 - 11:11
3. We subtract the len of the word and the word without the required letter
Excelchat Expert 19/09/2018 - 11:12
4. We multiply the final result by 2
Excelchat Expert 19/09/2018 - 11:12
So for word YOUNG the final result is 2
User 19/09/2018 - 11:12
User 19/09/2018 - 11:12
Thank you so much
Excelchat Expert 19/09/2018 - 11:12
If it is YOUNGY, the result is 4
Excelchat Expert 19/09/2018 - 11:12
I hope that will help you
Excelchat Expert 19/09/2018 - 11:12
Sorry that I didn't get that quiz..
User 19/09/2018 - 11:13
That totally fine. I'm sure if I was able to show you a video of someone using it two type it would make sense. Describing it was just difficult.
User 19/09/2018 - 11:14
THank you so much for your help!
Excelchat Expert 19/09/2018 - 11:14
Thanks for coming to Excelchat. You may now end the session and leave any comments or feedback. Have a nice day!

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
The Allstate Corporation
United Parcel Service
Dell Inc