When data in cells from two different columns match, take sum of corresponding data from third column

[Python49]

Hello,

I have been having some trouble figuring out a SUMSIF or SUMPRODUCT formula to accomplish this, as well as searching on google and the forums for some time. Can someone please help me with the formula to accomplish the following:

Three columns of data, A, B, C:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Chris[/TD]
[TD]Chris[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD][/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Doug[/TD]
[TD][/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Jim[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]


I'd like the values from the third column to be sum'd when cells from A and B match please. In the above example it would be 100 for Chris and 250 for Jim, totaling to 350.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this

Excel Workbook
	A	B	C	D	E
1
2	Chris	Chris	100		350
3	Dan		150
4	Doug		200
5	Jim	Jim	250
6	Mary		300
Sum

 

[Python49]

Welcome to the MrExcel board!

Try this

Sum

	A	B	C	D	E
Chris	Chris
Dan
Doug
Jim	Jim
Mary

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:49px;"><col style="width:49px;"><col style="width:40px;"><col style="width:56px;"><col style="width:40px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]350[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]150[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]250[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]300[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =SUMPRODUCT(--(A2:A6=B2:B6),C2:C6) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Peter, thanks for the speedy reply! Upon doing this I have now found why I wasn't able to get it to work before. I didn't explain I think what I was hoping to do correctly. In the above example, what I actually need, but not sure if excel can do it (although I'm hopeful since I haven't seen much that excel can't do!!) is for the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Chris[/TD]
[TD]$50[/TD]
[TD][/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Dan [/TD]
[TD]$25[/TD]
[TD][/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]Doug[/TD]
[TD]$50[/TD]
[TD][/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]Jim [/TD]
[TD]$35[/TD]
[TD][/TD]
[TD]Chris[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]$40[/TD]
[TD][/TD]
[TD]n/a[/TD]
[/TR]
</tbody>[/TABLE]


Example above, column A has the names of people, column B has how much they get paid if they attend a meeting/job/event/etc. If their name shows up on column C then they get paid the amount next to their name, but their name might not be in order for column C. Is there a way to get the total? For example, in the above example, because Mary and Chris are both on the list, the total amount to be paid is $50+$40 = $90 total for Mary and Chris.

 

[Peter_SSs]

I didn't explain I think what I was hoping to do correctly.

That does make a difference - it is all we have to go on.

Try this version.

Excel Workbook
	A	B	C	D	E
1
2	Chris	$50	Mary		90
3	Dan	$25	n/a
4	Doug	$50	n/a
5	Jim	$35	Chris
6	Mary	$40	n/a
Sum

 

[Python49]

That does make a difference - it is all we have to go on.

Try this version.

Sum

	A	B	C	D	E
Chris	Mary
Dan	n/a
Doug	n/a
Jim	Chris
Mary	n/a

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"><col style="width:56px;"><col style="width:24px;"><col style="width:37px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]$50 [/TD]

[TD="align: right"]90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]$25 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]$50 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]$35 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]$40 [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =SUMPRODUCT(--ISNUMBER(MATCH(A2:A6,C2:C6,0)),B2:B6) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Marvelous! Great thanks

 

[Peter_SSs]

Marvelous! Great thanks

No problem. Glad it helped.

 

[Python49]

I had one more question which I'd LOVE if you can help me with! Google sheets is great but one of the WORST features that's extremely aggravating is the tool that automatically turns the mouse cursor into a hand when it hovers between cells and then can drag a cell anywhere you want it. This causes lots of mistakes for me if I misclick and then it now drags a cell that I was simply trying to select and now it's moved formulas and deleted stuff. Even worse is that sometimes I don't notice it until later when a formula has stopped working. Please tell me there's a way to disable this function?

 

[Peter_SSs]

Sorry, I don't use Google Sheets.