Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use sumif with criteria including sheet name
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use sumif with criteria including sheet name
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. "SleepyCat" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use sumif with criteria including sheet name
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! "Thomas [PBD]" wrote: 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. "SleepyCat" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use sumif with criteria including sheet name
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",Endsh eet!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 "SleepyCat" wrote: 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! "Thomas [PBD]" wrote: 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. "SleepyCat" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use sumif with criteria including sheet name
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 "Jay_C" wrote in message ... 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",Endsh eet!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 "SleepyCat" wrote: 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! "Thomas [PBD]" wrote: 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. "SleepyCat" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average with 2 criteria including month | Excel Worksheet Functions | |||
Sumif with two criteria including a date range | Excel Discussion (Misc queries) | |||
How do I count frequency based on 2 criteria (including month) | Excel Worksheet Functions | |||
How do I do a sumif function not including hidden rows? | Excel Worksheet Functions | |||
coutif with 3 criteria including brackets | Excel Worksheet Functions |