do i use vlookup or sumproduct???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 *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
Click to expand...


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 said:
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
Click to expand...


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)
 
Back
Top