HOW TO: If value in a row matches another row's value, sum up column value in both rows

[yleong]

Hi, I am new to Excel and need help with a formula.

I need the formula to sum up the values from the column Bs of rows whose column A info match each other.

A example of this is:


Column A Column B Column C (Formula)
car..............$5
car..............$4
car..............$3.........$12

If this is possible, please let me know

Also, I understand if Column C will be

Column C (Formula)
$12
$12
$12

Do let me know if you can help with either situation, although I would prefer that for each set of duplicates in column A, only one value will appear in column C. Thanks in advance!

 

[Aladin Akyurek]

In C2 enter and copy down:

=SUMIF($A$2:$A$4,B2,$B$2:$B$4)

 

[T. Valko]

Hi, I am new to Excel and need help with a formula.

I need the formula to sum up the values from the column Bs of rows whose column A info match each other.

A example of this is:


Column A Column B Column C (Formula)
car..............$5
car..............$4
car..............$3.........$12

If this is possible, please let me know

Also, I understand if Column C will be

Column C (Formula)
$12
$12
$12

Do let me know if you can help with either situation, although I would prefer that for each set of duplicates in column A, only one value will appear in column C. Thanks in advance!

Try this...

Book1
	A	B	C
2	Car	5	_
3	Car	4	_
4	Car	3	12
5	Bus	5	_
6	Bus	5	10
7	Bike	7	7
Sheet1

Formula entered in C2 and copied down:

=IF(A2=A3,"",SUMIF(A$2:A$7,A2,B$2:B$7))

 

[yleong]

Thanks for your reply, it worked! But I don't quite understand the formula even after trying it out on Excel.

=IF(A2=A3,"",SUMIF(A$2:A$7,A2,B$2:B$7)) <- for this, why is the [value if true] just this ""?

Thanks for taking the time to reply!

 

[T. Valko]

Thanks for your reply, it worked!

You're welcome. Thanks for the feedback!

But I don't quite understand the formula even after trying it out on Excel.

=IF(A2=A3,"",SUMIF(A$2:A$7,A2,B$2:B$7)) <- for this, why is the [value if true] just this ""?

Thanks for taking the time to reply!

We use that test so that the "subtotal" will be placed on the last row for each item.

Like this...

=IF(A2=A3 = TRUE = Leave this cell blank
=IF(A3:A4 = TRUE = Leave this cell blank
=IF(A4=A5 = FALSE = Do the SUMIF for CAR and put the result in this cell
=IF(A5=A6 = TRUE = Leave this cell blank
=IF(A6=A7 = FALSE = Do the SUMIF for BUS and put the result in this cell
=IF(A7=A8 = FALSE = Do the SUMIF for BIKE and put the result in this cell

 

[yleong]

I see now! But this would mean we will have to sort by column A before doing the summing then, right? Thanks for your assistance it meant a lot

 

[T. Valko]

I see now! But this would mean we will have to sort by column A before doing the summing then, right? Thanks for your assistance it meant a lot

Yes, the data would need to be sorted or grouped together for that to work.

I assumed the data was sorted or grouped together and you want the subtotal on the last row for each item based on this exhibit in your post:

Column A Column B Column C (Formula)
car..............$5
car..............$4
car..............$3.........$12

 

[snoopyhr]

In the event that you are not allowed to sort the list you can created a sorted list on the side as shown, just an alternative, may not be the way you asked for , I just post this for info if interested.

Excel Workbook
	A	B	C	D	E	F
1	Items	Some number			Unique List	Totals
2	Car	27			Boat	14
3	Car	15			Car	80
4	Boat	14			Home	29
5	Motorcycle	25			Motorcycle	167
6	Car	28			RV
7	Motorcycle	48
8	Motorcycle	36
9	RV	22
10	Car	10
11	Motorcycle	13
12	RV	12
13	Motorcycle	22
14	RV	33
15	Home	29
16	RV	33
17	Motorcycle	23
Sheet3

 

[jtidfold]

Couldn't you get the same "unique list" simply by doing an advanced filter instead of an array formula?

 

[snoopyhr]

Sure can but if you assign the name to the range then you will see the convinient of doing this way, as it becomes dynamics and the list gets updated upon change . no need to regenerate the procedure for advanced filter. I like things to be dynamic rather than static.