Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that will check if 2 or more cells containing names match then add the adjacent cells to matching name together.
Solved by F. D. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
28/09/2018 - 03:27
Welcome to Excelchat, I see that your question is about Formulas.
Excelchat Expert
28/09/2018 - 03:27
I can help you with that problem, I'll be able to give you a solution and an explanation. Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
28/09/2018 - 03:28
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
28/09/2018 - 03:29
Based on what you've shared, you need to sum up the values of names that match together. Do you believe that will address your problem?
User
28/09/2018 - 03:30
yes
Excelchat Expert
28/09/2018 - 03:30
Got it. I'm going to create the formula needed to get the output you want. This should take me less than 20 minutes. If we need more than 20 minutes, you'll be able to extend the session up to 60 total minutes.
Excelchat Expert
28/09/2018 - 03:31
I'll be working on this shared worksheet. When our session is over you'll be able to access the solution in your session history by downloading the Excel File.
Excelchat Expert
28/09/2018 - 03:38
I have added the formula in F5 and G5. Here's the formula we need: =ArrayFormula(IFERROR(INDEX($B$5:$B$14, MATCH(0,COUNTIF($F$4:F4, $B$5:$B$14), 0)),""))
Excelchat Expert
28/09/2018 - 03:38
and =SUMIFS(C5:C14,$B$5:$B$14,F5)
Excelchat Expert
28/09/2018 - 03:38
Does this solution solve your problem?
User
28/09/2018 - 03:39
the sum isnt right for jan and joe.
Excelchat Expert
28/09/2018 - 03:39
Oh wait I need to lock the cells, sorry for that
Excelchat Expert
28/09/2018 - 03:40
Here's the correct formula for column G: =SUMIFS($C$5:$C$14,$B$5:$B$14,F5)
Excelchat Expert
28/09/2018 - 03:40
Please use this instead: =if(F5="","", SUMIFS($C$5:$C$14,$B$5:$B$14,F5))
Excelchat Expert
28/09/2018 - 03:41
We use INDEX and MATCH formula to get the list of unique names from column B.
User
28/09/2018 - 03:41
can i get rid of the name column you made and get a formula with out it?
Excelchat Expert
28/09/2018 - 03:41
and SUMIFS formula to get the sum of the values that match with the name
User
28/09/2018 - 03:42
i only want the total without listing names again
User
28/09/2018 - 03:42
and a blank cell if it has been used
User
28/09/2018 - 03:42
like the green shows
Excelchat Expert
28/09/2018 - 03:42
Let me check if that is possible
User
28/09/2018 - 03:44
or if that not possible then leave the name in the correct row like the orange
User
28/09/2018 - 03:44
but i prefer like the green
Excelchat Expert
28/09/2018 - 03:47
It will look like this, will repeat for every name, but I will see if we can have it blank once it has been sum up above.
User
28/09/2018 - 03:50
ok, what if the name only appears once?
User
28/09/2018 - 03:50
never mind
Excelchat Expert
28/09/2018 - 03:50
I will have to change the array, but it should still sum that once
User
28/09/2018 - 03:51
i can manage if the name only appears once i think
User
28/09/2018 - 03:51
ok
User
28/09/2018 - 03:51
perfect
Excelchat Expert
28/09/2018 - 03:51
Yes sure Im almost done with that formula
Excelchat Expert
28/09/2018 - 03:51
Just a minute while I finalize it so it will only appear once.
User
28/09/2018 - 03:51
thanks
Excelchat Expert
28/09/2018 - 03:54
Here's the formula: =IF(COUNTIF($B$4:B5,B5)>1,"",SUMIF($B$5:$B$16,B5,$C$5:$C$16))
Excelchat Expert
28/09/2018 - 03:54
Does this solution solve your problem?
Excelchat Expert
28/09/2018 - 03:55
So the formula would SUM the values of matching names unless that name already appear on top, meaning it has been sum up already.
Excelchat Expert
28/09/2018 - 03:55
Do you have any other questions about this problem and the solution?
User
28/09/2018 - 03:58
ok, my names are in c and values in H
Excelchat Expert
28/09/2018 - 03:58
Where do you need the formula?
Excelchat Expert
28/09/2018 - 03:58
Column I?
User
28/09/2018 - 03:58
yes
Excelchat Expert
28/09/2018 - 04:00
Until what row #?
User
28/09/2018 - 04:02
i have a list that goes to 521 but i will be using the formula on list that could go much longer
User
28/09/2018 - 04:02
late
User
28/09/2018 - 04:02
later
Excelchat Expert
28/09/2018 - 04:02
For reference this is what this formula is about: IF(COUNTIF(range, criterion)>1,leave blank,else SUMIF(range, criterion, sum_range)
User
28/09/2018 - 04:02
some names repeat 10-20 times
Excelchat Expert
28/09/2018 - 04:04
Then maybe you can use this formula instead: =IF(COUNTIF($C$2:C3,C3)>1,"",SUMIF($C$3:$C$1000,B8,$H$3:$H$1000))
Excelchat Expert
28/09/2018 - 04:04
I have set the last row to be row 1000
Excelchat Expert
28/09/2018 - 04:05
But if your data would go beyond that, you just have to change the range from 1000 to 20000 for example
User
28/09/2018 - 04:05
what is the b8 in that formula I have a date in my b column
Excelchat Expert
28/09/2018 - 04:05
=IF(COUNTIF($C$2:C3,C3)>1,"",SUMIF($C$3:$C$1000,C3,$H$3:$H$1000))
Excelchat Expert
28/09/2018 - 04:05
Sorry that was for the formula earlier
Excelchat Expert
28/09/2018 - 04:08
$C$2:C3 -first cell with the name
C3 - cell reference with the name
$C$3:$C$1000 - range of the names (you can change the row # 1000 to the last row # with your data)
C3 - cell reference with the name
$H$3:$H$1000 - range with the values that we need the SUM of (you can change the row # 1000 to the last row # with your data)
Excelchat Expert
28/09/2018 - 04:08
That's what the cell reference and ranges are referring to.
Excelchat Expert
28/09/2018 - 04:08
Is there anything else that I can assist you with regarding this issue?
Excelchat Expert
28/09/2018 - 04:09
$C$2:C3 -first cell with the name
Excelchat Expert
28/09/2018 - 04:09
C3 - cell reference with the name
Excelchat Expert
28/09/2018 - 04:09
$C$3:$C$1000 - range of the names (you can change the row # 1000 to the last row # with your data)
Excelchat Expert
28/09/2018 - 04:09
C3 - cell reference with the name
Excelchat Expert
28/09/2018 - 04:09
$H$3:$H$1000 - range with the values that we need the SUM of (you can change the row # 1000 to the last row # with your data)
Excelchat Expert
28/09/2018 - 04:10
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.
User
28/09/2018 - 04:11
if the name repeats
User
28/09/2018 - 04:11
more than twice it doesnt seem to work
Excelchat Expert
28/09/2018 - 04:11
Can you provide the sample?
User
28/09/2018 - 04:12
let me look at my sheet real quick
Excelchat Expert
28/09/2018 - 04:13
Sure, let me know if you're good with it
Excelchat Expert
28/09/2018 - 04:13
Does the name starts in Row 3 like our example?
User
28/09/2018 - 04:14
ok, i found the problem
Excelchat Expert
28/09/2018 - 04:15
What is it?
Excelchat Expert
28/09/2018 - 04:15
We can also use PIVOT TABLE for the output you need without using any formula.
User
28/09/2018 - 04:16
Can i get it to correlate with date, OR only get the names that are bunched together to add... so the max's in yellow and the Max;s in blue
User
28/09/2018 - 04:17
the green is what i need
Excelchat Expert
28/09/2018 - 04:20
Here's the formula for that: =IF(COUNTIFS($C$2:C3,C3,$B$2:B3,B3)>1,"",SUMIFS($H$3:$H$1000,$C$3:$C$1000,C3,$B$3:$B$1000,B3))
User
28/09/2018 - 04:20
ok, it that based on the date column or just if the name is grouped?
User
28/09/2018 - 04:20
oh i see = datre
User
28/09/2018 - 04:21
date*
Excelchat Expert
28/09/2018 - 04:21
We just added the dates in the criteria: Sum only if 1. Same name, 2. Same Data 3. Not a duplicate SUM
Excelchat Expert
28/09/2018 - 04:21
No sum only the values if they have the same name and date.
Excelchat Expert
28/09/2018 - 04:22
Does this solution solve your problem?
User
28/09/2018 - 04:23
Yes, thank you
Excelchat Expert
28/09/2018 - 04:23
You're welcome! :)
Excelchat Expert
28/09/2018 - 04:23
Do you have any other questions about this problem and the solution?
User
28/09/2018 - 04:23
not at the moment
Excelchat Expert
28/09/2018 - 04:23
Thanks for coming to Excelchat. Please give your kind feedback for our service. Have a good day! :)
User
28/09/2018 - 04:24
you too
Excelchat Expert
28/09/2018 - 04:24
Just a reminder, we are unable to end this session on our end until you do so please do not close this window without ending the session properly (Red X or End Session). Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
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.
