Use a combination of autofilter and the SUBTOTAL() function:

1. put a header cell on top of each column
2. click the header cell and pull down:
Data Filter Autofilter Custom and select contains smith
3. this should display only the smith rows
4. in an un-used cell enter:
=SUBTOTAL(9,B2:B100)


This form of SUBTOTAL will add-up the values in a column, but ignore cells
filtered out.


--
Gary's Student


"jim sturtz" wrote:

i am trying to determine if a cell has a partial value in it, for example
cola colb
john smith 1
john brown 2
bob smith 3
carly simon 4
john clark 5
tom smith 6


processing column a, top to bottom,for 'smith' would return 10, or 'john'
would return 8.

any help would be appreciated.

btw, if column a is a currency field can you find the '$'. i tried the
search function on that kind of field and it doesnt think it is text so
struck out.

can you 'convert' a cell to text for comparison purposes without having to
go change the true underlying format?

thanks.

jim

microsoft.public.excel.worksheet.functions