Adding numbers based on partial text in adjacent cells
 

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!

Adding numbers based on partial text in adjacent cells
 

A new wrinkle: Using SUMIF I'm able to do the calculations using the entire
text string (I still want to key in on one word though) on a single sheet. I
need to do the calculation across 24 sheets. When I choose a range across
multiple sheets, I get a #VALUE error "A value used in the formula is of the
wrong data type". Each individual sheet works fine. I only get the error
when I try to check two or more sheets.

"s31064" wrote:

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!

Adding numbers based on partial text in adjacent cells
 

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.

"s31064" wrote:

A new wrinkle: Using SUMIF I'm able to do the calculation on a single sheet. I
need to do the calculation across 24 sheets. When I choose a range across
multiple sheets, I get a #VALUE error "A value used in the formula is of the
wrong data type". Each individual sheet works fine. I only get the error
when I try to check two or more sheets.

Adding numbers based on partial text in adjacent cells
 

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)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"s31064" wrote in message
...
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.

"s31064" wrote:

A new wrinkle: Using SUMIF I'm able to do the calculation on a single
sheet. I
need to do the calculation across 24 sheets. When I choose a range
across
multiple sheets, I get a #VALUE error "A value used in the formula is of
the
wrong data type". Each individual sheet works fine. I only get the
error
when I try to check two or more sheets.