Formula about sum value if two column matched

Dear buddies,
I have an excel formula question , which I have tried several times.

I want to sum the amount (Column C ) if the value is Column B & Column C is matched ..

Like the picture, the return value should be $300

Does anyone can help me , thanks in advance !


JPEG.JPG

kary

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mock-up of the expected results.

Having said that, I *think* this is what you want/have. I would do this with a helper column, then sum that...

	A	B	C	D
1	aa	aa	100	100
2	bb	zz	200	500
3	cc	xx	300	0
4	dd	yy	400	0
5	ee	bb	500	0

D1=IFERROR(INDEX(C:C,MATCH(A1,B:B,0)),)
copied down
Then sum that column

The following array entered formula* is another way to arrive at the result:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell containing the formula is in the edit mode.
Note this assumes that the 2014 values begin in A2.
Let us know if you have any questions.

That results in $600 - it is adding three values instead of just the two in red.

Applied to the file attached to post #3 the formula in F5 would read:

Formula:

Please Login or Register  to view this content.

If I understand the criteria the sum should be 600 because 110600324 is in both B8 and C7.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. If Ford's and/or my solution works on your actual data then please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

or can try
=SUMPRODUCT(ISNUMBER(MATCH($C$5:$C$9,$B$5:$B$9,0))*($D$5:$D$9))

Mmmm, well the image and instructions in the opening post were very misleading, then!!!