# Excel - IF Function Problem - Expert Solution

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

Hi! I have a large set of messy data to sort through. I am trying to work with an IF statement to return a number(embedded in a cell of a different column) if a certain word is present in first said column. Is that possible?
Solved by M. W. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 23/05/2018 - 10:37
Hello
User 23/05/2018 - 10:37
hi
Excelchat Expert 23/05/2018 - 10:38
Welcome to excelchat.
User 23/05/2018 - 10:38
thanks!
Excelchat Expert 23/05/2018 - 10:38
So, I want to understand the problem clearly and to do that I need to see the data.
Excelchat Expert 23/05/2018 - 10:38
Would you be able to show me some sample or the original file?L
User 23/05/2018 - 10:39
i could show sample.
Excelchat Expert 23/05/2018 - 10:39
Thank you, that would be a great help. Please remember to replicate your original file as much as possible.
User 23/05/2018 - 10:40
can you see if i type into the google sheet?
Excelchat Expert 23/05/2018 - 10:40
I can see once you are finished typing.
User 23/05/2018 - 10:42
so i basically have what is in the sheet.
Excelchat Expert 23/05/2018 - 10:42
listening...
User 23/05/2018 - 10:43
i want to add the total number of cases for retailer X as quickly and efficiently as possible without summing all of the individual cases
User 23/05/2018 - 10:44
are you there?
Excelchat Expert 23/05/2018 - 10:44
Does that mean you want to get sum of column B?
Excelchat Expert 23/05/2018 - 10:44
Yeah, I'm here.
User 23/05/2018 - 10:45
i just added to the sheet and will hightlight the ones I wanted summed
User 23/05/2018 - 10:45
done
Excelchat Expert 23/05/2018 - 10:46
Okay, its hard to get the problem as of now.
Excelchat Expert 23/05/2018 - 10:46
For starter, is there other retailer's than retailer X?
User 23/05/2018 - 10:46
there are hundreds of lines of data. I dont want to click all individually
User 23/05/2018 - 10:47
there are other retailers involved as well. but i only want retailer x
Excelchat Expert 23/05/2018 - 10:47
I'm sorry, I'm asking again, can you show me the original file?
User 23/05/2018 - 10:47
i cannot.
Excelchat Expert 23/05/2018 - 10:48
Okay I understand.
Excelchat Expert 23/05/2018 - 10:48
User 23/05/2018 - 10:48
it is laid out in exactly the same way
User 23/05/2018 - 10:48
i did
User 23/05/2018 - 10:48
there are other retailers involved as well. but i only want retailer x
Excelchat Expert 23/05/2018 - 10:48
Yes you did. Thanks for that.
Excelchat Expert 23/05/2018 - 10:48
I have more questions to clearly understand the problem.
User 23/05/2018 - 10:49
Excelchat Expert 23/05/2018 - 10:49
thank you.
Excelchat Expert 23/05/2018 - 10:49
I assume retailer x is a name.
User 23/05/2018 - 10:49
yes
Excelchat Expert 23/05/2018 - 10:50
And cases of x if the cases owned by that retailer.
User 23/05/2018 - 10:50
yes
Excelchat Expert 23/05/2018 - 10:50
Thank you. I think I would be able to give you a formula.
User 23/05/2018 - 10:51
thank you! what do i do?
Excelchat Expert 23/05/2018 - 10:51
You don't have to now.
User 23/05/2018 - 10:52
okay please teach me we only have 5 min
User 23/05/2018 - 10:52
appreciate it!
Excelchat Expert 23/05/2018 - 10:52
Don't worry, I will teach you and also we can extend time.
Excelchat Expert 23/05/2018 - 10:53
So, in an empty cell we write "cases of x". Let's say in cell D4.
Excelchat Expert 23/05/2018 - 10:54
Then we write the formula =SUMIF(A:A,"="&D4,B:B) in another cell.
Excelchat Expert 23/05/2018 - 10:54
This will give the total of Retailer x, which is what you wanted.
Excelchat Expert 23/05/2018 - 10:54
Making sense?
User 23/05/2018 - 10:56
trying to figure out on my sheet. how do you do it the whole way down the sheet?
Excelchat Expert 23/05/2018 - 10:57
I already have accounted that and wrote the formula accordingly.
Excelchat Expert 23/05/2018 - 10:58
This will count the numbers in whole column B.
User 23/05/2018 - 10:58
oh awesome. let me see if i can replicate in my spreadsheet
User 23/05/2018 - 10:58
this is great!
Excelchat Expert 23/05/2018 - 10:58
Sure, take your time. Don't forget to ask questions when you are stuck.
User 23/05/2018 - 11:00
why do you write cases of x?
Excelchat Expert 23/05/2018 - 11:01
Because you wanted to get total for cases of x.
Excelchat Expert 23/05/2018 - 11:02
If it is retailer Y, then you write cases of y
Excelchat Expert 23/05/2018 - 11:05
Anything else?
User 23/05/2018 - 11:05
min came up as "0" for some reason
Excelchat Expert 23/05/2018 - 11:06
May I know what you wrote?
User 23/05/2018 - 11:07
wait may have gotten something. is there a way to add a column and another criteria into the formula
Excelchat Expert 23/05/2018 - 11:07
Yeah there is.
Excelchat Expert 23/05/2018 - 11:08
What do you have in mind?
User 23/05/2018 - 11:08
I am typing in the excel right now!
Excelchat Expert 23/05/2018 - 11:09
I know you are sensitive about your data, but to be able to help and resolve problem efficiently, there is no alternative to be able to work with original file.
Excelchat Expert 23/05/2018 - 11:09
User 23/05/2018 - 11:11
okay i will upload somethin and change the names.
User 23/05/2018 - 11:11
will that work?
Excelchat Expert 23/05/2018 - 11:12
Thank you, as long as that contains what you need to do it will suffice.
Excelchat Expert 23/05/2018 - 11:12
Also note that we have only one extension left.
Excelchat Expert 23/05/2018 - 11:13
That will give total 24 minutes to solve the problem.
User 23/05/2018 - 11:14
i have just pasted the exact format of the excel sheet
User 23/05/2018 - 11:14
i want a sum of the numbers i am about to highlight
Excelchat Expert 23/05/2018 - 11:14
Okay.
User 23/05/2018 - 11:15
all are in yellow!
User 23/05/2018 - 11:15
and the page goes on for about 1000 rows
Excelchat Expert 23/05/2018 - 11:16
Thank you, I got it.
User 23/05/2018 - 11:16
you are the best! haha im learning here!!
Excelchat Expert 23/05/2018 - 11:16
We are about 300 experts here and we try our best. :)
Excelchat Expert 23/05/2018 - 11:18
Okay, I have the solution.
User 23/05/2018 - 11:18
awesome! so for clarity i have changed the fake addresses to indicated that they are separate such as "1 bridget ct" "2 bridget ct" etc.
User 23/05/2018 - 11:19
awesome!!
Excelchat Expert 23/05/2018 - 11:19
Just realized something.
Excelchat Expert 23/05/2018 - 11:20
When you said you have highlighted the cells you need.
User 23/05/2018 - 11:20
what?
Excelchat Expert 23/05/2018 - 11:20
I see you have highlighted customers total
User 23/05/2018 - 11:20
yes i just need a sum of the highlighted cells
User 23/05/2018 - 11:20
yes!
Excelchat Expert 23/05/2018 - 11:20
You have several many retailers, right?
User 23/05/2018 - 11:21
but for example there could be a retailer Y in the sheet that i do not need a total for
Excelchat Expert 23/05/2018 - 11:21
Do you want each retailers customer totals separately or the total sum for all retailers.
User 23/05/2018 - 11:21
yes, exactly
Excelchat Expert 23/05/2018 - 11:21
Then the given info is not enough.
User 23/05/2018 - 11:22
i just added it in as an example
Excelchat Expert 23/05/2018 - 11:23
I'm sorry but I can't really write a formula since there is no pattern I can found.
User 23/05/2018 - 11:23
and the customer total is not highlighted because it is not needed
Excelchat Expert 23/05/2018 - 11:24
I know, to be able to write a formula I need identifiers. The customer total in column D is a identifier.
Excelchat Expert 23/05/2018 - 11:25
Its alright, what I now need another identifier which will separate retailer x from retailer Y.
User 23/05/2018 - 11:25
so what if you add another identifier in column c that was retailer X
User 23/05/2018 - 11:25
does that work?
User 23/05/2018 - 11:25
yes!
Excelchat Expert 23/05/2018 - 11:26
I mean alongside the customer total, in the same row.
User 23/05/2018 - 11:26
if it has 1) retailer X and 2) customer total then it should be added
Excelchat Expert 23/05/2018 - 11:27
Please take a look at M21.
Excelchat Expert 23/05/2018 - 11:27
sorry M20.
User 23/05/2018 - 11:27
can a separate function be added in column E
User 23/05/2018 - 11:27
im looking
Excelchat Expert 23/05/2018 - 11:28
Now when I look at X in M20, I know it is for retailer X.
Excelchat Expert 23/05/2018 - 11:28
There should also be something to differentiate other retailers as it is in M20
User 23/05/2018 - 11:28
got ya, is there a way to create a formula within M to do that?
Excelchat Expert 23/05/2018 - 11:29
That depends.
Excelchat Expert 23/05/2018 - 11:30
Again, I have to look at the whole sheet and identify patterns to be able to write a formula.
User 23/05/2018 - 11:30
what about what i just did.
User 23/05/2018 - 11:30
in the sheet.
Excelchat Expert 23/05/2018 - 11:30
What did you do?
User 23/05/2018 - 11:31
cell E 10 i did =c2
User 23/05/2018 - 11:31
and dragged all the way down
User 23/05/2018 - 11:31
so the retailer returned in the same row
Excelchat Expert 23/05/2018 - 11:32
Okay. Let's see if that will make difference.
User 23/05/2018 - 11:32
yay! team work
Excelchat Expert 23/05/2018 - 11:34
=SUMIFS(H:H,D:D,"=CUST TOTALS:",E:E,"=Retailer Y")
Excelchat Expert 23/05/2018 - 11:34
In K3 this is the formula
Excelchat Expert 23/05/2018 - 11:34
It will give total for Y.
Excelchat Expert 23/05/2018 - 11:35
Changing Retailer Y in formula to Retailer X will give total for X.
Excelchat Expert 23/05/2018 - 11:35
We only have 2 more minutes left.
Excelchat Expert 23/05/2018 - 11:35
After that we will get disconnected.
User 23/05/2018 - 11:36
thanks so much i will see if this can work for me. i really appreciate it!
Excelchat Expert 23/05/2018 - 11:36
Excelchat Expert 23/05/2018 - 11:36
Have a great day.
User 23/05/2018 - 11:36
you too!

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.