I have a similar problem. I have:

A...........B.............C............D.....
2 CE......2 CE........2 A.........2 S...

In L:N, I would like a total of all A:J that end in CE, A and S. I tried
changing your sum statement, but get at total of 0.08 instead of 4 for CE.

"Biff" wrote:

it appears that all must be a number with a space.

Yes, that's correct. That's based on the limited info from the post.

Biff

"Don Guillett" wrote in message
...
Biff,
This works if all cells are properly populated. But if you have
apple without a number or 1apple, it failed in my test. So, it appears
that all must be a number with a space.

Happy Thanksgiving to all from Texas


--
Don Guillett
SalesAid Software

"Biff" wrote in message
...
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