sumif where cells contain Number & Text

[Fred]

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]

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

 

[Don Guillett]

try this. Change the range to suit>place 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]

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

 

[Biff]

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

 

[Fred]

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

 

[T. Valko]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thanks! I figured it out while waiting for a response. I had an "a"
instead
of "A". The following allows me to search for the cells without having a
seperate cell identifying the criteria.

=SUM(IF(ISNUMBER(SEARCH("A",$A4:$J4)),
--SUBSTITUTE($A4:$J4," "&IF(RIGHT($A4:$J4,1)="A","A",""),"")))