do i use vlookup or sumproduct???

I have these two sheets (as an example)

Sheet 1 :

..........A................ B........... C .........D
......Product .........Total
......Codes ..........sales
3 ..apples
4..pears
5 ..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

In column b on sheet 1, i wish to total the sales values - so for example
column b 3 i wish to add up ALL the sales of apples (20+8+13 =answer 41)....

What formula do i need to put in column b3 to achieve this?

many thanks

Nel post news:[email protected]
*Lazclark* ha scritto:

> I have these two sheets (as an example)
>
> Sheet 1 :
>
> .........A................ B........... C .........D
> .....Product .........Total
> .....Codes ..........sales
> 3 ..apples
> 4..pears
> 5 ..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
>
> In column b on sheet 1, i wish to total the sales values - so for
> example column b 3 i wish to add up ALL the sales of apples (20+8+13
> =answer 41)....
>
> What formula do i need to put in column b3 to achieve this?
>
> many thanks


If the two sheets are on the same workbook you can use SUMIF or SUMPRODUCT:

=SUMIF(Sheet2!$S$48:$S$53,A3,Sheet2!$T$48:$T$53)

=SUMPRODUCT((Sheet2!$S$48:$S$53=A3)*(Sheet2!$T$48:$T$53))

But the two sheests are on separate workbooks you should use SUMPRODUCT:

=SUMPRODUCT(([SourceWorkBook]Sheet2!$S$48:$S$53=A3)*([SourceWorkBook]Sheet2!$T$48:$T$53))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Lazclark wrote:
> I have these two sheets (as an example)
>
> Sheet 1 :
>
> .........A................ B........... C .........D
> .....Product .........Total
> .....Codes ..........sales
> 3 ..apples
> 4..pears
> 5 ..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
>
> In column b on sheet 1, i wish to total the sales values - so for example
> column b 3 i wish to add up ALL the sales of apples (20+8+13 =answer 41)....
>


> What formula do i need to put in column b3 to achieve this?
>
> many thanks


Hi,
One way would be to use SUMIF()... Something along the lines of this in
B3

=SUMIF(RangeOfProductsOnSheet2,"Apples",RangeOfSalesOnSheet2)

Regards,
Bondi

=SUMIF(Sheet2!A:A,A3,Sheet2!B:B)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Lazclark" <[email protected]> wrote in message
news:[email protected]...
> I have these two sheets (as an example)
>
> Sheet 1 :
>
> .........A................ B........... C .........D
> .....Product .........Total
> .....Codes ..........sales
> 3 ..apples
> 4..pears
> 5 ..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
>
> In column b on sheet 1, i wish to total the sales values - so for example
> column b 3 i wish to add up ALL the sales of apples (20+8+13 =answer
41)....
>
> What formula do i need to put in column b3 to achieve this?
>
> many thanks