Try this:

................B....................C
1..........Date................Date
2........3 apples...........1 peach
3......10 oranges.........5 pears
4........1 apple............1 pear
5........1 orange..........7 apples

A10 = apple
A11 = orange
A12 = pear

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER) in B10:

=SUM(IF(ISNUMBER(SEARCH($A10,B$2:B$5)),--SUBSTITUTE(B$2:B$5,
" "&$A10&IF(RIGHT(B$2:B$5)="s","s",""),"")))

Copy across then down.

Here's a sample file:

Sum with text.xls 14kb

http://cjoint.com/?lxaTj5QLUh

Biff

"Fred" wrote in message
oups.com...
I have a sheet of data, as follows
Row 1 is the dates between now and the end of the year, by day (60 'ish
columns)
Column A contains a series of names
Columns B thru AZ (approx) contain a number and a category (i.e. "3
Apples", "1 Apple", "2 Oranges" or "1 Pear") against each of the names
in column A

I want to sum the number of Apples, Oranges and Pears for each day at
the bottom of each day.

Do I have to split the number & category into separate cells to achieve
this ?

I tried =SUMIF(B2:B26,"*Apple*",B2:B26) but that produced a sum of 0