how can i do a vlookup with sum ?

[jameshanley39]

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

 

[Ragdyer]

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

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

 

[jameshanley39]

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

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

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.

 

[Niek Otten]

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


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

 

[jameshanley39]

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