sum vlookup

[jwillits]

Need helping summing with vlookup or index/match.

I am trying to add values that come up multiple times in the same column. Column A are the numbers that come up multiple times, and each has a value to the right of it in Column B. The bottom example is what I am trying to do. I just need a formula that sums up the numbers in Column B based off of Column A. To reiterate, I am trying to obtain a formula for Column D. An example is the number 3 in Column A appears 2 times, and has values to the right of it of 7 and 2. So the formula I would need would give me 9, as found in Column D.

Column A	Column B
1	5
3	7
3	2
4	5
5	3
4	7
7	8

Column C	Column D
1	5
3	9
4	12
5	3
7	8

 

[etaf]

Not sure what version of excel you have
can you let us know , as the solution will be different with later versions

this is only on the later versions of excel
to extract a UNIQUE list of a column use

=UNIQUE(A2:A8,FALSE)

use a SUMIF()

in your column D2

SUMIF( $A$1:$A$200, C2, $B$1:$B$200)

Assuming 200 is the number of rows in the data

Vlookup-ETAF.xlsx
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	1	5	1	5
3	3	7	3	9
4	3	2	4	12
5	4	5	5	3
6	5	3	7	8
7	4	7
8	7	8
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=UNIQUE(A2:A8,FALSE)
D2:D6	D2	=SUMIF(A2:A8,C2,B2:B8)
Dynamic array formulas.

or you can use a pivot table to get the result

Vlookup-ETAF.xlsx
	A	B	C	D	E	F	G	H
1	Column A	Column B	Column C	Column D
2	1	5	1	5		Row Labels	Sum of Column B
3	3	7	3	9		1	5
4	3	2	4	12		3	9
5	4	5	5	3		4	12
6	5	3	7	8		5	3
7	4	7				7	8
8	7	8				Grand Total	37
9
Sheet2

 

[jwillits]

It is a later version with Windows 10. The formula you provided worked great, thanks!

 

[etaf]

you are welcome

 

[Peter_SSs]

It is a later version with Windows 10.

It would help if you added the version to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It must be Microsoft 365 but if you want to check, go to File -> Account and look for this

The formula you provided worked great, thanks!

One slight issue with that could be that if the number of unique values in column A increases or decreases you would need to increase/decrease the number of formulas in column D, or include an IF(.. condition in the column D formula.

Another option is to have this single formula in C2 and all the results should automatically spill in both columns.

21 08 28.xlsm
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	1	5	1	5
3	3	7	3	9
4	3	2	4	12
5	4	5	5	3
6	5	3	7	8
7	4	7
8	7	8
SUM

Cell Formulas
Range	Formula
C2:D6	C2	=CHOOSE({1,2},UNIQUE(A2:A8),SUMIF(A2:A8,UNIQUE(A2:A8),B2:B8))
Dynamic array formulas.