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?
|