how can i do a vlookup with sum ?

e.g.

Given this table
a 1
b 30
b 31
c 2

I want to look for all occurrences of "b" in the first column, and
return the sum of corresponding values in the second column.

So I want the output to be 61

or better, an output of =30+31


TIA

With letters in Column A, and numbers in Column B, try this:

=Sumif(A:A,"b",B:B)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> how can i do a vlookup with sum ?
>
> e.g.
>
> Given this table
> a 1
> b 30
> b 31
> c 2
>
> I want to look for all occurrences of "b" in the first column, and
> return the sum of corresponding values in the second column.
>
> So I want the output to be 61
>
> or better, an output of =30+31
>
>
> TIA
>

> <jameshanle...@yahoo.co.uk> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > how can i do a vlookup with sum ?
>
> > e.g.
>
> > Given this table
> > a 1
> > b 30
> > b 31
> > c 2
>
> > I want to look for all occurrences of "b" in the first column, and
> > return the sum of corresponding values in the second column.
>
> > So I want the output to be 61
>
> > or better, an output of =30+31
>
> > TIA-

On Jun 3, 5:25 am, "Ragdyer" <RagD...@cutoutmsn.com> wrote:
> With letters in Column A, and numbers in Column B, try this:
>
> =Sumif(A:A,"b",B:B)
> --
> HTH,
>
> RD

Thanks, but
=sumif(A1:B5,"b",B1:B5)
doesn't go as far as making a result of , say, =B1+B4+B5. It just
displays the value of B1+B4+B5 in the cell, and shows the whole
formula when clicked

It doesn't simplify the formula from sumif to =B1+B4+B5 , so it's not
possible to check it / see its intermediate step(s) / its workings
out.
The only thing I need to see is =B1+B4+B5, I could press F2 and see
those cells highlighted. But this doesn't seem possible.

Take an intermediate step; in column C:

=IF(A1="b",B1,"")
Fill down as far as your data goes

Sum that column. That gives you both the total and the individual values

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
|> <jameshanle...@yahoo.co.uk> wrote in message
| >
| > news:(E-Mail Removed)...
| >
| >
| >
| > > how can i do a vlookup with sum ?
| >
| > > e.g.
| >
| > > Given this table
| > > a 1
| > > b 30
| > > b 31
| > > c 2
| >
| > > I want to look for all occurrences of "b" in the first column, and
| > > return the sum of corresponding values in the second column.
| >
| > > So I want the output to be 61
| >
| > > or better, an output of =30+31
| >
| > > TIA-
|
| On Jun 3, 5:25 am, "Ragdyer" <RagD...@cutoutmsn.com> wrote:
| > With letters in Column A, and numbers in Column B, try this:
| >
| > =Sumif(A:A,"b",B:B)
| > --
| > HTH,
| >
| > RD
|
| Thanks, but
| =sumif(A1:B5,"b",B1:B5)
| doesn't go as far as making a result of , say, =B1+B4+B5. It just
| displays the value of B1+B4+B5 in the cell, and shows the whole
| formula when clicked
|
| It doesn't simplify the formula from sumif to =B1+B4+B5 , so it's not
| possible to check it / see its intermediate step(s) / its workings
| out.
| The only thing I need to see is =B1+B4+B5, I could press F2 and see
| those cells highlighted. But this doesn't seem possible.
|
|
|

On Jun 3, 4:40 pm, "Niek Otten" <nicol...@xs4all.nl> wrote:
> Take an intermediate step; in column C:
>
> =IF(A1="b",B1,"")
> Fill down as far as your data goes
>
> Sum that column. That gives you both the total and the individual values
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>


works nicely, thanks.

It seems that in excel, an if statement can't make a decision about
another cell's value. The "then" portion of the if statement can only
be assigning a value to that cell where the if is written.
e.g. you can't write in a cell. =if(A1=2,B1=3,C1=6)
Would that be right?

Seems quite wise, avoids a spreadsheet equivalent of spaghetti code
(code that gets hard to read because GOTO is abused).

It has an american feel to it. Like the bill of rights, but for a
society of cells !

A cell can only look at other cells and change its value.
A cell can't infringe on another cell's rights! its right to
determine who/what it is!!