Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF across multiple worksheets workaround help
What I would like to do is a SUMIF from sheet First to Last and
everything in between, but I know that isn't possible, so I've been working on a workaround. This is what I have so far that gives me a #VALUE! error: =SUMPRODUCT(SUMIF(INDIRECT("'PD #"&ROW(INDIRECT("1:2"))&"'!A:A"),A8,INDIRECT("' PD #"&ROW(INDIRECT("1:2"))&"'!F:F"))) My sheets are named PD #1, PD #2, PD #3, etc. (Right now I'm just trying to sum the first two sheets to see if it works.) If the value in column A on sheet PD #whatever for any given row equals the value in A8 then sum the values in column F of sheet PD #whatever. If anybody has any help it would be greatly appreciated. I'm on Windows XP and using Excel 2003. Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF across multiple worksheets workaround help
Will the formula then only work on computers that have that add-in
installed? Because I need it to work on other people's computers as well. I think the hard returns are a result of pasting the formula into Google, because I didn't type it in that way. And there are spaces between PD and # both in the formula and in the worksheet names. I'm still getting an error. Thanks for letting me know about the add-in. Grant Domenic wrote: The syntax for the formula is correct. Although, when I copied and pasted the formula into a worksheet I found two hard returns. Remove them, and make sure a space exists between PD and #, and you should be okay. Also, if you download and install the free add-in Morefunc.xll, you can use the following formula... =SUMPRODUCT(--(THREED('PD #1:PD #3'!A2:A100)=A8),THREED('PD #1:PD #3'!F2:F100)) The add-in can be download at the following link... http://xcell05.free.fr/english/index.html Hope this helps! In article . com, wrote: What I would like to do is a SUMIF from sheet First to Last and everything in between, but I know that isn't possible, so I've been working on a workaround. This is what I have so far that gives me a #VALUE! error: =SUMPRODUCT(SUMIF(INDIRECT("'PD #"&ROW(INDIRECT("1:2"))&"'!A:A"),A8,INDIRECT("' PD #"&ROW(INDIRECT("1:2"))&"'!F:F"))) My sheets are named PD #1, PD #2, PD #3, etc. (Right now I'm just trying to sum the first two sheets to see if it works.) If the value in column A on sheet PD #whatever for any given row equals the value in A8 then sum the values in column F of sheet PD #whatever. If anybody has any help it would be greatly appreciated. I'm on Windows XP and using Excel 2003. Thank you! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF across multiple worksheets workaround help
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF across multiple worksheets workaround help
Domenic, This solution seemed to be the answer I was looking for, for similar
problem. I down loaded the add in and tried the formula. However I got a #REF! error message. Can you maybe explain this to me? Tracy "Domenic" wrote: The syntax for the formula is correct. Although, when I copied and pasted the formula into a worksheet I found two hard returns. Remove them, and make sure a space exists between PD and #, and you should be okay. Also, if you download and install the free add-in Morefunc.xll, you can use the following formula... =SUMPRODUCT(--(THREED('PD #1:PD #3'!A2:A100)=A8),THREED('PD #1:PD #3'!F2:F100)) The add-in can be download at the following link... http://xcell05.free.fr/english/index.html Hope this helps! In article . com, wrote: What I would like to do is a SUMIF from sheet First to Last and everything in between, but I know that isn't possible, so I've been working on a workaround. This is what I have so far that gives me a #VALUE! error: =SUMPRODUCT(SUMIF(INDIRECT("'PD #"&ROW(INDIRECT("1:2"))&"'!A:A"),A8,INDIRECT("' PD #"&ROW(INDIRECT("1:2"))&"'!F:F"))) My sheets are named PD #1, PD #2, PD #3, etc. (Right now I'm just trying to sum the first two sheets to see if it works.) If the value in column A on sheet PD #whatever for any given row equals the value in A8 then sum the values in column F of sheet PD #whatever. If anybody has any help it would be greatly appreciated. I'm on Windows XP and using Excel 2003. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print all charts in a workbook (multiple worksheets) | Charts and Charting in Excel | |||
Functions across multiple worksheets | Excel Worksheet Functions | |||
Sumif with multiple worksheets | Excel Worksheet Functions | |||
how do I arrange multiple worksheets from the same workbook | Excel Discussion (Misc queries) | |||
Extracting data from multiple worksheets into a list | Excel Worksheet Functions |