**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.*