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.
