Sumif using wildcard

Hi,

I am trying to use a wildcard function (*) in a sumif function, eg SUMIF(B:B,A1*,C:C) but excel converts the A1* to "A1*" rather that the content of cell A1.

If for example the content of A1 was "smith" and i want to find the sum of all cells that contain smith in column B, the sumif works if i enter "smith*" instead of A1* but the problem i have is that i have a multitude of cells in column a where they will correspond to only part of the cell in colum b and therefore need to use a wildcard with the cell reference....

Please can somone PLEASE help...

cheers

Hi!

Try this:

=SUMIF(B:B,A1&"*",C:C)

Biff

"claireanddoug" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I am trying to use a wildcard function (*) in a sumif function, eg
> SUMIF(B:B,A1*,C:C) but excel converts the A1* to "A1*" rather that the
> content of cell A1.
>
> If for example the content of A1 was "smith" and i want to find the sum
> of all cells that contain smith in column B, the sumif works if i enter
> "smith*" instead of A1* but the problem i have is that i have a
> multitude of cells in column a where they will correspond to only part
> of the cell in colum b and therefore need to use a wildcard with the
> cell reference....
>
> Please can somone PLEASE help...
>
> cheers
>
>
> --
> claireanddoug
> ------------------------------------------------------------------------
> claireanddoug's Profile:
> http://www.excelforum.com/member.php...o&userid=27490
> View this thread: http://www.excelforum.com/showthread...hreadid=470038
>