EXCEL >> SUM VLOOKUP ?

  A p o l l o 11:58 21 Feb 2007
Locked

Hi

I have two columns A, B
A has Employee Names
B has expenditure through the month
I would like to total the cells for expenditure where employee name is 'John'
I assumed it would be something like

=SUM(VLOOKUP("JOHN",A1:B100,2))
but that only returns the last expense.

Help!

  ed-0 12:08 21 Feb 2007

Wait for VoG™

bump ^^^^^

  A p o l l o 12:20 21 Feb 2007

Sorry let me explain a bit futher.....

| A | B |
Mick 500
John 240
Andy 200
John 100
Mick 50

Total needed of all 'John' figures

I can do this manually, but sometimes there are several hundred entries (It's not me that designs the original spreadsheet - I just get to sort it out !)

  GEEKSTA 12:23 21 Feb 2007

something like,
highlight all of johns figures and press the "autosum" button, looks like a weiird "E"
??

just a suggestion.

  bezics 12:25 21 Feb 2007

In column C type:

=IF(A1='John', B1, "")

(assuming your list starts in row 1)

Populate column C with this formula to the bottom of your list - the row number will automatically update for each row you go down. Then just sum up the results.

  VoG II 12:27 21 Feb 2007

=SUMIF(A1:A100,"John",B1:B100)

  A p o l l o 12:32 21 Feb 2007

Many Thanks

Simple answer as always and worked perfectly.

Regards

  xania 16:39 21 Feb 2007

If you name your columns i.e Names and Amount, then in C1 insert the name you are looking ofr and name this cell Name, then you can insert in C2 the formula

=SUMIF(Names,Name,Amount)

to generate a more general purpose form of the same thing.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?