use sumif with criteria including sheet name

[SleepyCat]

I have a workbook which will be added new worksheets to by others. The
worksheet names are all month names.

I want to do a sumif according to the name of the worksheets, i.e. according
to the month specified by the worksheet name.

I am using the following formula, and it doesn't work:
=SUMIF(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),
"=*Sept*", STATISTICS:BLANK!C1)
with an intention to sum up the C1's in all sheets with the sheet's name
including the string "Sept".

Could anybody please help me out with this, thanks a bunch in advance!
Theresa

 

[Thomas [PBD]]

SleepyCat,

Are you attempting to Sum the "Sept" sheets in the same workbook, or across
multiple workbooks?
I noticed two things first off, SUMIF requires <range>,
<Criteria>,<Sum_Range>. You are placing the Criteria in the Range section.
Therefore this is going to error out.

 

[SleepyCat]

First a big THANK YOU to Thomas[PBD] for your answering my question.

It is within only one workbook, not multiple files. I am aware that with
SUMIF we got to have <range>, <Criteria>,<Sum_Range>. I believe the key
question here can be simply represented by:
SUMIF(BeginSheet:EndSheet!A1, "Sept", BeginSheet:EndSheet!B1)
Can anybody explain why we got this error?
Thanks in advance for helping!

SleepyCat,

Are you attempting to Sum the "Sept" sheets in the same workbook, or across
multiple workbooks?
I noticed two things first off, SUMIF requires <range>,
<Criteria>,<Sum_Range>. You are placing the Criteria in the Range section.
Therefore this is going to error out.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

I have a workbook which will be added new worksheets to by others. The
worksheet names are all month names.

I want to do a sumif according to the name of the worksheets, i.e. according
to the month specified by the worksheet name.

I am using the following formula, and it doesn't work:
=SUMIF(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),
"=*Sept*", STATISTICS:BLANK!C1)
with an intention to sum up the C1's in all sheets with the sheet's name
including the string "Sept".

Could anybody please help me out with this, thanks a bunch in advance!
Theresa

 

[Jay_C]

I am also trying to accomplish a similar task. I got the formula to give a
value by using
SUMIF(Beginsheet!A1:Beginsheet!A5,"Criteria",Endsheet!A10:Endsheet!A15)

But there is a hitch. The SUMIF equation only works if the amount of rows
in (range, criteria [sum_range]) are equal. So while I was able to get it to
spit out a number, it would not calculate all 5 rows since my criteria is
only a single value (probably seen as one row). So in other words, the
equation outputted only the value in the first row of [sum_range].

-Jay

First a big THANK YOU to Thomas[PBD] for your answering my question.

It is within only one workbook, not multiple files. I am aware that with
SUMIF we got to have <range>, <Criteria>,<Sum_Range>. I believe the key
question here can be simply represented by:
SUMIF(BeginSheet:EndSheet!A1, "Sept", BeginSheet:EndSheet!B1)
Can anybody explain why we got this error?
Thanks in advance for helping!

SleepyCat,

Are you attempting to Sum the "Sept" sheets in the same workbook, or across
multiple workbooks?
I noticed two things first off, SUMIF requires <range>,
<Criteria>,<Sum_Range>. You are placing the Criteria in the Range section.
Therefore this is going to error out.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

I have a workbook which will be added new worksheets to by others. The
worksheet names are all month names.

I want to do a sumif according to the name of the worksheets, i.e. according
to the month specified by the worksheet name.

I am using the following formula, and it doesn't work:
=SUMIF(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),
"=*Sept*", STATISTICS:BLANK!C1)
with an intention to sum up the C1's in all sheets with the sheet's name
including the string "Sept".

Could anybody please help me out with this, thanks a bunch in advance!
Theresa

 

[T. Valko]

Not sure what you're trying to do but that's how SUMIF works.

By default, the sum_range will be the same size as the range even if you
don't specify it as such.

=SUMIF(A1:A5,"x",B1)

The sum_range will be the same size as the range, A1:A5, so the sum_range is
B1:B5.

SUMIF only tests for a single criteria unless you manipulate it to accept
more than one criteria:

...........A..........B
1........x...........1
2........y...........3
3........z...........2
4........y...........3
5........x...........2

=SUMIF(A1:A5,"x",B1:B5) = 3
=SUMIF(A1:A5,"x",B1) = 3

Multiple criteria:

=SUM(SUMIF(A1:A5,{"x","y"},B1:B5)) = 9
=SUM(SUMIF(A1:A5,{"x","y"},B1)) = 9


--
Biff
Microsoft Excel MVP

I am also trying to accomplish a similar task. I got the formula to give a
value by using
SUMIF(Beginsheet!A1:Beginsheet!A5,"Criteria",Endsheet!A10:Endsheet!A15)

But there is a hitch. The SUMIF equation only works if the amount of rows
in (range, criteria [sum_range]) are equal. So while I was able to get it
to
spit out a number, it would not calculate all 5 rows since my criteria is
only a single value (probably seen as one row). So in other words, the
equation outputted only the value in the first row of [sum_range].

-Jay

First a big THANK YOU to Thomas[PBD] for your answering my question.

It is within only one workbook, not multiple files. I am aware that with
SUMIF we got to have <range>, <Criteria>,<Sum_Range>. I believe the key
question here can be simply represented by:
SUMIF(BeginSheet:EndSheet!A1, "Sept", BeginSheet:EndSheet!B1)

error: #VALUE!

Can anybody explain why we got this error?
Thanks in advance for helping!

SleepyCat,

Are you attempting to Sum the "Sept" sheets in the same workbook, or
across
multiple workbooks?
I noticed two things first off, SUMIF requires <range>,
<Criteria>,<Sum_Range>. You are placing the Criteria in the Range
section.
Therefore this is going to error out.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


:

I have a workbook which will be added new worksheets to by others.
The
worksheet names are all month names.

I want to do a sumif according to the name of the worksheets, i.e.
according
to the month specified by the worksheet name.

I am using the following formula, and it doesn't work:
=SUMIF(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),
"=*Sept*", STATISTICS:BLANK!C1)
with an intention to sum up the C1's in all sheets with the sheet's
name
including the string "Sept".

Could anybody please help me out with this, thanks a bunch in
advance!
Theresa