Excel - IF Function Problem - Expert Solution

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.

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.