Vlookup or Hlookup

[Anthony]

Hi,

is it possible to have a formula that can check down one
column for a specific word and each time this 'word' is
found then the formula will add up the values in the
adjoining cells.

example
Column A Column B

Mr Jones 34
Mr Smith 50
Mr Smith 60
Mr Potter 10
Mr smith 11

so I want to check in column A the total points that mr
Smith has got and the result would be 110

Any help apreciated

Thanks
Anthony

 

[Alan Beban]

Hi,

is it possible to have a formula that can check down one
column for a specific word and each time this 'word' is
found then the formula will add up the values in the
adjoining cells.

example
Column A Column B

Mr Jones 34
Mr Smith 50
Mr Smith 60
Mr Potter 10
Mr smith 11

so I want to check in column A the total points that mr
Smith has got and the result would be 110

Any help apreciated

Thanks
Anthony

=SUMPRODUCT(EXACT(A1:A5,"Mr Smith")*(B2:B7))

Alan Beban

 

[hgrove]

Anthony wrote...

is it possible to have a formula that can check down one column
for a specific word and each time this 'word' is found then the
formula will add up the values in the adjoining cells. ...
so I want to check in column A the total points that mr Smith
has got and the result would be 110

If case sensitivity isn't an issue,

=SUMIF(A1:A5,"Mr Smith",B1:B5

 

[Myrna Larson]

Hi, Harlan:

I'm not sure what the OP means by "values in the adjoining cells". The "cells"
vs "cell" is my problem. My original take was that he means to sum *several*
columns to the right, say B:F, whenever there's a match in column A.

If that's the case, then the following array formula (entered with
CTRL+SHIFT+ENTER) would work:

=SUM((A1:A30="a")*B1:F30)

or he would need one SUMIF formula for each column.

 

[Myrna Larson]

Just a comment on the formula I proposed: it will SUM all rows where there is
a match in column A. If "a" occurs in rows 3 and 5, you will get
SUM(B3:F3,B5:F5). If you want two sums, one for B3:F3 and another for B5:F5,
it won't work as you want.