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

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

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

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

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

Assume you have:

............L.....M.....N
1........CE....A.....S

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

=SUM(IF(ISNUMBER(SEARCH(L1,$A1:$J1)),
--SUBSTITUTE($A1:$J1," "&L1,""),""))

Copy across to N2.

The results will be (based on your sample):

............L.....M.....N
1........CE....A.....S
2.........4......2......2

Biff

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

try this. Change the range to suitplace in a REGULAR module
then just use as a regular function =gn("orange") or =gn("apple") NOT appleS

Function gn(y)
application.volatile 'may not be necessary
Dim mn As Long
For Each c In Range("c2:c22")
If InStr(c, y) 0 Then
mn = mn + Val(Left(c, InStr(c, " ")))
End If
Next
gn = mn
End Function


--
Don Guillett
SalesAid Software

"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

Biff/Don

Thanks guys for the help, Biff, the formula works a treat, although I
don't understand the Substitute action, it's all part of the learning
curve. The restrictions outlined were already identified, so not a
problem.

Thanks again
Regards
Fred

You're welcome. Thanks for the feedback!

Biff

"Fred" wrote in message
oups.com...

Biff/Don

Thanks guys for the help, Biff, the formula works a treat, although I
don't understand the Substitute action, it's all part of the learning
curve. The restrictions outlined were already identified, so not a
problem.

Thanks again
Regards
Fred