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
Please answer my question then.
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
okay ask away
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
Please also note that your data is safe with us.
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
Thanks for your patience.
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.
