how can i do a vlookup with sum ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jameshanley39@yahoo.co.uk - 03 Jun 2007 04:53 GMT

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

Reply to this Message

Ragdyer - 03 Jun 2007 05:25 GMT

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

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

Signature

HTH,

RD

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

> how can i do a vlookup with sum ?
>
[quoted text clipped - 14 lines]
>
> TIA

Reply to this Message

jameshanley39@yahoo.co.uk - 03 Jun 2007 13:54 GMT

> <jameshanle...@yahoo.co.uk> wrote in message
>
[quoted text clipped - 18 lines]
>
> > TIA-

> With letters in Column A, and numbers in Column B, try this:
>
[quoted text clipped - 3 lines]
>
> 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.

Reply to this Message

Niek Otten - 03 Jun 2007 16:40 GMT

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|> <jameshanle...@yahoo.co.uk> wrote in message
| >
[quoted text clipped - 38 lines]
| 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.

Reply to this Message

jameshanley39@yahoo.co.uk - 03 Jun 2007 18:27 GMT

> Take an intermediate step; in column C:
>
[quoted text clipped - 8 lines]
> 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!!

Reply to this Message

Rate this thread: