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!

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!

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.

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.