sum vlookup

jwillits

New Member
Joined
Jul 10, 2018
Messages
34
Office Version
  1. 2010
Platform
  1. Windows
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 AColumn B
15
37
32
45
53
47
78



Column CColumn D
15
39
412
53
78
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,979
Office Version
  1. 365
Platform
  1. MacOS
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
ABCD
1Column AColumn BColumn CColumn D
21515
33739
432412
54553
65378
747
878
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=UNIQUE(A2:A8,FALSE)
D2:D6D2=SUMIF(A2:A8,C2,B2:B8)
Dynamic array formulas.



or you can use a pivot table to get the result
Vlookup-ETAF.xlsx
ABCDEFGH
1Column AColumn BColumn CColumn D
21515Row LabelsSum of Column B
3373915
43241239
54553412
6537853
74778
878Grand Total37
9
Sheet2
 
Last edited:

jwillits

New Member
Joined
Jul 10, 2018
Messages
34
Office Version
  1. 2010
Platform
  1. Windows
It is a later version with Windows 10. The formula you provided worked great, thanks!
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,979
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,125
Office Version
  1. 365
Platform
  1. Windows
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
1630123962163.png



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
ABCD
1Column AColumn BColumn CColumn D
21515
33739
432412
54553
65378
747
878
SUM
Cell Formulas
RangeFormula
C2:D6C2=CHOOSE({1,2},UNIQUE(A2:A8),SUMIF(A2:A8,UNIQUE(A2:A8),B2:B8))
Dynamic array formulas.
 

Forum statistics

Threads
1,144,163
Messages
5,722,853
Members
422,461
Latest member
kelleys315

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top