# Summing columns using a vlookup

#### ubmtc22

##### New Member
on my y axis i have 5 companies (See below) on the x-axis I have values

Co A 1 2 5
Co B 2 3 4
Co C 6 5 2
Co D 8 7 9
Co E 1 8 12

I want to sum each column by using a Vlookup because my sum formula is on a separate page

I want to make sure the names of the companies on both sheets match and if the name changes the formula would bomb out, hence the vlookup vs a common summation formula.

Thanks

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### dave3009

##### Well-known Member
You would probably be better using an OFFSET SUM
Excel Workbook
ABCD
1NameSales1Sales2Sales3
2Co A125
3Co B234
4Co C652
5Co D879
6Co E1812
Sheet1
Excel 2007
Excel Workbook
AB
2CompanyTotal Sales
3Co C13
Sheet2
Excel 2007
Cell Formulas
RangeFormula
B3=SUM(OFFSET(Sheet1!\$A\$1, MATCH(\$A\$3,Sheet1!\$A:\$A, 0)-1, 1, 1, 3))

#### Matty

##### Well-known Member
Or you could try:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 67px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">Name</TD><TD style="FONT-FAMILY: Verdana">Sales1</TD><TD style="FONT-FAMILY: Verdana">Sales2</TD><TD style="FONT-FAMILY: Verdana">Sales3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana">Co A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">5</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">Co B</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">4</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana">Co C</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana">Co D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">9</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana">Co E</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">12</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana"> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD style="FONT-FAMILY: Verdana"> </TD><TD style="FONT-FAMILY: Verdana"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana">Name</TD><TD style="FONT-FAMILY: Verdana">Total Sales</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Verdana">Co A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">8</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Verdana">Co B</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-FAMILY: Verdana">Co C</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">13</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-FAMILY: Verdana">Co D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">24</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-FAMILY: Verdana">Co E</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">21</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

B9 copied down:

=SUM(IF(A\$2:A\$6=A9,B\$2:D\$6))

Which requires committing with Ctrl+Shift+Enter.

Matty

#### pgc01

##### MrExcel MVP
Hi ubmtc22
Welcome to the board

I want to sum each column by using a Vlookup because my sum formula is on a separate page

In this case I don't think that the formula is in another page is relevant.

Assuming the values in the table are all numbers, here's another formula solution. In Sheet2!B2:

=SUMPRODUCT((Sheet1!A2:A6=A2)*Sheet1!B2:D6)

Using Dave's layout:

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,449
Messages
5,837,320
Members
430,494
Latest member
abhi_is_yours

### 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.

### Which adblocker are you using?

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

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