vlookup totals?
 

I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding
product code on more than one occasion in the column.

I need the second column of the first sheet to calculate the totals
corresponding to the product codes in the second sheet.....

So, rather than just return one value, i need vlookup to total all of them
where the product code matches - is this possible?

vlookup totals?
 

In second column on Sheet1

=Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100)

This match code in (sheet1) A1 against codes on sheet2 and sum the values in
column B (but sure if this what you want)

If simply want count of number of times product occurs then:

=Sumproduct(--(sheet2!$a$1:$a$100=a1))

Or

=COUNTIF(Sheet2!$A$1:$A$4,"=" &A1)

HTH

"Lazclark" wrote:

I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding
product code on more than one occasion in the column.

I need the second column of the first sheet to calculate the totals
corresponding to the product codes in the second sheet.....

So, rather than just return one value, i need vlookup to total all of them
where the product code matches - is this possible?

vlookup totals?
 

Toppers - you live up to your name! Thanks for the speedy response...
Unfortunately my brain isn't working and i I still can't get it to work -
here's an example of what i want

Sheet 1 :

A B C D
Product Total
Codes sales

1 apples
2 pears
3 bananas

Sheet 2:


s t u v
Product Sales
Codes
48 apples 20
49 pears 15
50 bananas 23
51 apples 8
52 apples 13
53 bananas 15
For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be
38...

What formula do i need to type in sheet one column b1 for example?

Many thanks!

"Toppers" wrote:

In second column on Sheet1

=Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100)

This match code in (sheet1) A1 against codes on sheet2 and sum the values in
column B (but sure if this what you want)

If simply want count of number of times product occurs then:

=Sumproduct(--(sheet2!$a$1:$a$100=a1))

Or

=COUNTIF(Sheet2!$A$1:$A$4,"=" &A1)

HTH

"Lazclark" wrote:

I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding
product code on more than one occasion in the column.

I need the second column of the first sheet to calculate the totals
corresponding to the product codes in the second sheet.....

So, rather than just return one value, i need vlookup to total all of them
where the product code matches - is this possible?

vlookup totals?
 

Sorry - those sheets should have looked like :

Sheet 1 :

..........A................ B........... C .........D
......Product .........Total
......Codes ..........sales
1 ..apples
2 ..pears
3 ..bananas

Sheet 2:


..............s...................t.............. u ...............v
.............Product... Sales
.............Codes
48 ......apples........ 20
49...... pears .........15
50..... bananas...... 23
51 ......apples .........8
52 .....apples .........13
53 ...bananas .........15

"Lazclark" wrote:

Toppers - you live up to your name! Thanks for the speedy response...
Unfortunately my brain isn't working and i I still can't get it to work -
here's an example of what i want

Sheet 1 :

A B C D
Product Total
Codes sales

1 apples
2 pears
3 bananas

Sheet 2:


s t u v
Product Sales
Codes
48 apples 20
49 pears 15
50 bananas 23
51 apples 8
52 apples 13
53 bananas 15
For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be
38...

What formula do i need to type in sheet one column b1 for example?

Many thanks!

"Toppers" wrote:

In second column on Sheet1

=Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100)

This match code in (sheet1) A1 against codes on sheet2 and sum the values in
column B (but sure if this what you want)

If simply want count of number of times product occurs then:

=Sumproduct(--(sheet2!$a$1:$a$100=a1))

Or

=COUNTIF(Sheet2!$A$1:$A$4,"=" &A1)

HTH

"Lazclark" wrote:

I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding
product code on more than one occasion in the column.

I need the second column of the first sheet to calculate the totals
corresponding to the product codes in the second sheet.....

So, rather than just return one value, i need vlookup to total all of them
where the product code matches - is this possible?

vlookup totals?
 

Hi Lazclark,

did you try sumif?
on the 2nd column of sheet 1 include:

=sumif(a2,sheet2!a2:a1000,sheet2!b2:b1000)

hth
regards from Brazil
Marcelo

"Lazclark" escreveu:

Sorry - those sheets should have looked like :

Sheet 1 :

.........A................ B........... C .........D
.....Product .........Total
.....Codes ..........sales
1 ..apples
2 ..pears
3 ..bananas

Sheet 2:


.............s...................t.............. u ...............v
............Product... Sales
............Codes
48 ......apples........ 20
49...... pears .........15
50..... bananas...... 23
51 ......apples .........8
52 .....apples .........13
53 ...bananas .........15

"Lazclark" wrote:

Toppers - you live up to your name! Thanks for the speedy response...
Unfortunately my brain isn't working and i I still can't get it to work -
here's an example of what i want

Sheet 1 :

A B C D
Product Total
Codes sales

1 apples
2 pears
3 bananas

Sheet 2:


s t u v
Product Sales
Codes
48 apples 20
49 pears 15
50 bananas 23
51 apples 8
52 apples 13
53 bananas 15
For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be
38...

What formula do i need to type in sheet one column b1 for example?

Many thanks!

"Toppers" wrote:

In second column on Sheet1

=Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100)

This match code in (sheet1) A1 against codes on sheet2 and sum the values in
column B (but sure if this what you want)

If simply want count of number of times product occurs then:

=Sumproduct(--(sheet2!$a$1:$a$100=a1))

Or

=COUNTIF(Sheet2!$A$1:$A$4,"=" &A1)

HTH

"Lazclark" wrote:

I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding
product code on more than one occasion in the column.

I need the second column of the first sheet to calculate the totals
corresponding to the product codes in the second sheet.....

So, rather than just return one value, i need vlookup to total all of them
where the product code matches - is this possible?

vlookup totals?
 

In cell B2 on Sheet1 and copy down

=SUMPRODUCT(--(Sheet2!$T$2:$T$7=A2),(Sheet2!$U$2:$U$7))

Change ranges of T & U to suit (product codes in T, Sales in U?)

HTH

"Lazclark" wrote:

Sorry - those sheets should have looked like :

Sheet 1 :

.........A................ B........... C .........D
.....Product .........Total
.....Codes ..........sales
1 ..apples
2 ..pears
3 ..bananas

Sheet 2:


.............s...................t.............. u ...............v
............Product... Sales
............Codes
48 ......apples........ 20
49...... pears .........15
50..... bananas...... 23
51 ......apples .........8
52 .....apples .........13
53 ...bananas .........15

"Lazclark" wrote:

Toppers - you live up to your name! Thanks for the speedy response...
Unfortunately my brain isn't working and i I still can't get it to work -
here's an example of what i want

Sheet 1 :

A B C D
Product Total
Codes sales

1 apples
2 pears
3 bananas

Sheet 2:


s t u v
Product Sales
Codes
48 apples 20
49 pears 15
50 bananas 23
51 apples 8
52 apples 13
53 bananas 15
For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be
38...

What formula do i need to type in sheet one column b1 for example?

Many thanks!

"Toppers" wrote:

In second column on Sheet1

=Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100)

This match code in (sheet1) A1 against codes on sheet2 and sum the values in
column B (but sure if this what you want)

If simply want count of number of times product occurs then:

=Sumproduct(--(sheet2!$a$1:$a$100=a1))

Or

=COUNTIF(Sheet2!$A$1:$A$4,"=" &A1)

HTH

"Lazclark" wrote:

I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding
product code on more than one occasion in the column.

I need the second column of the first sheet to calculate the totals
corresponding to the product codes in the second sheet.....

So, rather than just return one value, i need vlookup to total all of them
where the product code matches - is this possible?

vlookup totals?
 

... Should be:

=sumif(sheet2!a2:a1000,a2,sheet2!b2:b1000)



"Marcelo" wrote:

Hi Lazclark,

did you try sumif?
on the 2nd column of sheet 1 include:

=sumif(a2,sheet2!a2:a1000,sheet2!b2:b1000)

hth
regards from Brazil
Marcelo

"Lazclark" escreveu:

Sorry - those sheets should have looked like :

Sheet 1 :

.........A................ B........... C .........D
.....Product .........Total
.....Codes ..........sales
1 ..apples
2 ..pears
3 ..bananas

Sheet 2:


.............s...................t.............. u ...............v
............Product... Sales
............Codes
48 ......apples........ 20
49...... pears .........15
50..... bananas...... 23
51 ......apples .........8
52 .....apples .........13
53 ...bananas .........15

"Lazclark" wrote:

Toppers - you live up to your name! Thanks for the speedy response...
Unfortunately my brain isn't working and i I still can't get it to work -
here's an example of what i want

Sheet 1 :

A B C D
Product Total
Codes sales

1 apples
2 pears
3 bananas

Sheet 2:


s t u v
Product Sales
Codes
48 apples 20
49 pears 15
50 bananas 23
51 apples 8
52 apples 13
53 bananas 15
For example, i want the value returned in B1 sheet 1 to be 41 and b3 to be
38...

What formula do i need to type in sheet one column b1 for example?

Many thanks!

"Toppers" wrote:

In second column on Sheet1

=Sumproduct((sheet2!$a$1:$a$100=a1)*(sheet2!$b$1:$ b$100)

This match code in (sheet1) A1 against codes on sheet2 and sum the values in
column B (but sure if this what you want)

If simply want count of number of times product occurs then:

=Sumproduct(--(sheet2!$a$1:$a$100=a1))

Or

=COUNTIF(Sheet2!$A$1:$A$4,"=" &A1)

HTH

"Lazclark" wrote:

I have two spreadsheets - In one column on this 1st sheet, there is a product
code. On another spreadsheet, one of the columns uses the corresponding
product code on more than one occasion in the column.

I need the second column of the first sheet to calculate the totals
corresponding to the product codes in the second sheet.....

So, rather than just return one value, i need vlookup to total all of them
where the product code matches - is this possible?