sum & VLOOKUP

  • Thread starter Thread starter Mire
  • Start date Start date
M

Mire

Hi,
this is the sheet (A1="name"; A2="A ltd"; ... ; B1="jan"; ... ... ...):

name jan feb
Altd 5 8
Bltd 10 9
Cltd 7 11

In cells AA21 this formula (9 the result) VLOOKUP(A20,A1:C4,3,FALSE)) where
AA20 = "Bltd".

Now the problem: how can put in AA22 the SUM of "name" using VLOOKUP? (no
code VBA)

Example: if AA20 ="B ltd" the AA22 will be the sum of 10 and 9 = 19

Thanks in advance
M.
 
Hi

AA22=SUMPRODUCT((A1:A4=AA20)*(B1:B4+C1:C4))
or
AA22=SUMIF(A1:A4,AA20,B1:C4))


Arvi Laanemets
 
Mire,
One way
=SUM(OFFSET(INDIRECT("B"&MATCH(AA20,A1:A4,0)),0,0,1,12))
this will sum the numbers in 12 columns starting from colB of the matching
row.
HTH
Cecil
 
Very good!
Thanks to Cecil and Arvi

Cecilkumara Fernando said:
Mire,
One way
=SUM(OFFSET(INDIRECT("B"&MATCH(AA20,A1:A4,0)),0,0,1,12))
this will sum the numbers in 12 columns starting from colB of the matching
row.
HTH
Cecil
Click to expand...
 
Back
Top