All solutions SUM Expert Solution – Excel SUM Problems

Excel - SUM Function Problem - Expert Solution

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

Hi, What formula is best to sum 2 values in 2 separate cells based on a matched criteria? Thanks
Solved by A. Y. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/09/2018 - 09:03
Welcome to excelchat, I see your question is about summing two values based on criteria.
User 17/09/2018 - 09:03
Hello,
Excelchat Expert 17/09/2018 - 09:04
I can help you with the problem. I'll also provide a full explanation of the solution.
User 17/09/2018 - 09:04
Yes it is
Excelchat Expert 17/09/2018 - 09:04
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.
User 17/09/2018 - 09:04
Thank you so much
User 17/09/2018 - 09:04
Sure
Excelchat Expert 17/09/2018 - 09:04
Let me ask you a couple of quick questions to make sure I fully understand your problem. For starter, can you please show me the data?
User 17/09/2018 - 09:04
How do I show you
Excelchat Expert 17/09/2018 - 09:05
You can either send me the file or you can copy/paste on the blank spreadsheet on the right hand side.
User 17/09/2018 - 09:05
It is on Google Drive ...
Excelchat Expert 17/09/2018 - 09:06
In that case, you can share the link to the file, or copy/paste data in the blank spreadhsheet.
User 17/09/2018 - 09:06
I will share the link
User 17/09/2018 - 09:06
...
Excelchat Expert 17/09/2018 - 09:06
Thanks, please make sure you grant the necessary permission before sharing..
User 17/09/2018 - 09:06
Let me know what I need to do
Excelchat Expert 17/09/2018 - 09:07
You have to make the file publicly visible before sharing the link.
User 17/09/2018 - 09:07
https://docs.google.com/spreadsheets/d/1ZS3RF97-FLytX_uidMZk94LVYGWWTJ7SquA0hYWm1w8/edit?usp=sharing
Excelchat Expert 17/09/2018 - 09:07
Great!
Excelchat Expert 17/09/2018 - 09:08
Now please tell me what you need by referencing to appropriate sheet and cell names.
User 17/09/2018 - 09:08
On this tab
User 17/09/2018 - 09:08
STEP 3a - Deal Summary
Excelchat Expert 17/09/2018 - 09:08
I'm there..
User 17/09/2018 - 09:08
Cell G2
User 17/09/2018 - 09:09
That is where I would like the formula
User 17/09/2018 - 09:09
The formula needs to sum the following cells
User 17/09/2018 - 09:09
C8 and C14 from the following sheet
User 17/09/2018 - 09:09
STEP 3b - Project Costs
User 17/09/2018 - 09:10
Based on the address in cell C1 on both sheets matching
Excelchat Expert 17/09/2018 - 09:10
C1 might change?
User 17/09/2018 - 09:10
Yes
Excelchat Expert 17/09/2018 - 09:11
If the address doesn't match, then?
User 17/09/2018 - 09:11
Empty cell or maybe a message in the cell to explain that error
Excelchat Expert 17/09/2018 - 09:12
Explain error can be a vast array of information. But empty cell is a definite.
User 17/09/2018 - 09:12
Sure thing - I can add an IFERROR after
User 17/09/2018 - 09:12
Just something like "Addresses Don't Match"
Excelchat Expert 17/09/2018 - 09:13
Okay, this can be done.
User 17/09/2018 - 09:13
Excellent
Excelchat Expert 17/09/2018 - 09:14
So, based on what you have shared, you need to add cell C8 and C14 of STEP 3b - Project Costs in STEP 3a - Deal Summary sheets G12 if the address at C1 of both sheets are the same, otherwise you would like to see an error "Addresses Don't Match".
Excelchat Expert 17/09/2018 - 09:14
Do you believe that will address your problem?
User 17/09/2018 - 09:14
I believe so
User 17/09/2018 - 09:15
Into G2 not G12
Excelchat Expert 17/09/2018 - 09:15
Great, since I don't have edit access to your file, I will share the formula you need to use in G12.
Excelchat Expert 17/09/2018 - 09:15
Okay In G2.
Excelchat Expert 17/09/2018 - 09:15
My apologies.
User 17/09/2018 - 09:16
I have allowed you edit access
Excelchat Expert 17/09/2018 - 09:16
It would need 2/3 minutes.
User 17/09/2018 - 09:16
Sure
Excelchat Expert 17/09/2018 - 09:16
Thank you
Excelchat Expert 17/09/2018 - 09:17
=IF(C1='STEP 3b - Project Costs'!C1,'STEP 3b - Project Costs'!C8+'STEP 3b - Project Costs'!C14,"Addresses Don't Match")
Excelchat Expert 17/09/2018 - 09:17
This is the formula.
Excelchat Expert 17/09/2018 - 09:17
Let me tell you how this works.
User 17/09/2018 - 09:18
It need to go in 'Purchase/Holding Costs' not 'Asking Price'
User 17/09/2018 - 09:19
Perfect
Excelchat Expert 17/09/2018 - 09:19
My sincere apologies, I have corrected it.
User 17/09/2018 - 09:19
Thank you
Excelchat Expert 17/09/2018 - 09:19
Does this solution solve your problem?
User 17/09/2018 - 09:19
Excellent
User 17/09/2018 - 09:19
How did you do that
Excelchat Expert 17/09/2018 - 09:19
The formula I used is =IF(C1='STEP 3b - Project Costs'!C1,'STEP 3b - Project Costs'!C8+'STEP 3b - Project Costs'!C14,"Addresses Don't Match")
Excelchat Expert 17/09/2018 - 09:20
IT first checks whether C1 of Step 3a sheet is equal to C1 of Step 3b sheet.
Excelchat Expert 17/09/2018 - 09:21
C1='STEP 3b - Project Costs'!C1 this is the part of the formula.
User 17/09/2018 - 09:21
Sure
Excelchat Expert 17/09/2018 - 09:21
Then next part is 'STEP 3b - Project Costs'!C8+'STEP 3b - Project Costs'!C14
Excelchat Expert 17/09/2018 - 09:22
This tells excel to sum C8 and C14 of Step 3b sheet if the first part of the formula returns TRUE.
User 17/09/2018 - 09:22
I see
User 17/09/2018 - 09:22
and then the error if not
Excelchat Expert 17/09/2018 - 09:22
Meaning this part will be executed when the address matched.
Excelchat Expert 17/09/2018 - 09:22
Yeah, otherwise the error message is returned.
User 17/09/2018 - 09:23
So it only required a simple IF formula - nothing like Index or Match was required
Excelchat Expert 17/09/2018 - 09:23
Yes a IF formula is sufficient and efficient in this case.
User 17/09/2018 - 09:24
Excellent - I am really grateful for that
Excelchat Expert 17/09/2018 - 09:24
Do you have any other questions about this problem and the solution?
User 17/09/2018 - 09:24
This service is excellent
Excelchat Expert 17/09/2018 - 09:24
Thank you. We are the best.
Excelchat Expert 17/09/2018 - 09:24
:)
User 17/09/2018 - 09:24
Is it really expensive - as I have so many problems
Excelchat Expert 17/09/2018 - 09:25
I really don't have any information on the pricing. I am hired to solve problems.
User 17/09/2018 - 09:25
Sure thing - I need more friends like you !
Excelchat Expert 17/09/2018 - 09:25
You can mail the support team, I am sure they would be able to provide you are a good option.
User 17/09/2018 - 09:26
Perfect, OK well thanks for that I really appreciate your time
Excelchat Expert 17/09/2018 - 09:26
Me too! I hope to see you often in future.
Excelchat Expert 17/09/2018 - 09:26
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!
Excelchat Expert 17/09/2018 - 09:27
N.B.: To leave a feedback or comment, you need to click on END button. It is also essential to click on END button once the problem is resolved, otherwise I'm stuck here and won't be able to help others.

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