Adding numbers based on partial text in adjacent cells

S

s31064

I need to add the numbers in cells that are next to cells containing specific
text. I can do this with SUMIF, but that requires (I believe) matching the
entire string of text. I want to key on one particular word in that string.
I'm getting lumps from banging my head on the desk. HELP!
 
S

s31064

OK. I solved my original problem of keying in on one word of a text string
using SUMIF (I used "*"&ref_cell&"*" as the criteria). The formula still
won't work across multiple sheets though, and that's a lot more important to
me. I tried it as a straight formula and as an array. Either way I get the
same error. The complete formula I'm fighting with is
=SUMIF('01.02.2006:11.27.2006'!C11:C24,
"*"&B3&"*",'01.02.2006:11.27.2006'!D11:D24). Is there another function I
should be using, or am I going to have to nest 24 SUMIF statements into a
SUM? Seems like there should be a neater way.
 
R

RagDyer

The *easiest* solution is to "group" your sheets and enter a Sumif formula
in an "out-of-the-way" cell, say Z1, and then total that Z1 cell across your
entire WB.

Add a blank "dummy" sheet in front of your first sheet, and name it
Start
And add another after your last sheet and name it
End
And use something like this:

=SUM(Start:End!Z1)
 
Top