Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
I have a workbook that contains 3 worksheets, one for each vendor site. I am
trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$1002)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002="Peformer1",Sheet1!$R$3 :$R$1002,Sheet2!$R$3:$R$1002,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!$H$3:$H$1002"),"Pe
rformer1",INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"! $S$3:$S$1002")))/ SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT(" 1:3"))&"!$H$3:$H$1002"),"P erformer1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... I have a workbook that contains 3 worksheets, one for each vendor site. I am trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$10 02)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002 ="Peformer1",Sheet1!$R$3:$R$1002,Sheet2!$R$3:$R$10 02,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
I am not getting this to work. I have additional data in column S - does this
make a difference? "Bob Phillips" wrote: =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!$H$3:$H$1002"),"Pe rformer1",INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"! $S$3:$S$1002")))/ SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT(" 1:3"))&"!$H$3:$H$1002"),"P erformer1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... I have a workbook that contains 3 worksheets, one for each vendor site. I am trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$10 02)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002 ="Peformer1",Sheet1!$R$3:$R$1002,Sheet2!$R$3:$R$10 02,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
Still not working.....do I need to replace "Sheet" with an actual sheet name?
Sorry for being dense. I replaced Performer1 with the actual value in the cell - which is the person's name. "xvfcc1" wrote: I have a workbook that contains 3 worksheets, one for each vendor site. I am trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$1002)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002="Peformer1",Sheet1!$R$3 :$R$1002,Sheet2!$R$3:$R$1002,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
Yes, I used the fact that each sheet started with Sheet and suffixed by 1,2
and 3 in the formula. What are yours called? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... Still not working.....do I need to replace "Sheet" with an actual sheet name? Sorry for being dense. I replaced Performer1 with the actual value in the cell - which is the person's name. "xvfcc1" wrote: I have a workbook that contains 3 worksheets, one for each vendor site. I am trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$10 02)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002 ="Peformer1",Sheet1!$R$3:$R$1002,Sheet2!$R$3:$R$10 02,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
Sorry - their actual names are 13920, 13921, and 13922
"Bob Phillips" wrote: Yes, I used the fact that each sheet started with Sheet and suffixed by 1,2 and 3 in the formula. What are yours called? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... Still not working.....do I need to replace "Sheet" with an actual sheet name? Sorry for being dense. I replaced Performer1 with the actual value in the cell - which is the person's name. "xvfcc1" wrote: I have a workbook that contains 3 worksheets, one for each vendor site. I am trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$10 02)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002 ="Peformer1",Sheet1!$R$3:$R$1002,Sheet2!$R$3:$R$10 02,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
Just use
=SUMPRODUCT(SUMIF(INDIRECT("1392"&ROW(INDIRECT("1: 3"))-1&"!$H$3:$H$1002"),"P e rformer1",INDIRECT("1392"&ROW(INDIRECT("1:3"))-1&"!$S$3:$S$1002")))/ SUMPRODUCT(COUNTIF(INDIRECT("1392"&ROW(INDIRECT("1 :3"))-1"!$H$3:$H$1002"),"P erformer1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... Sorry - their actual names are 13920, 13921, and 13922 "Bob Phillips" wrote: Yes, I used the fact that each sheet started with Sheet and suffixed by 1,2 and 3 in the formula. What are yours called? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... Still not working.....do I need to replace "Sheet" with an actual sheet name? Sorry for being dense. I replaced Performer1 with the actual value in the cell - which is the person's name. "xvfcc1" wrote: I have a workbook that contains 3 worksheets, one for each vendor site. I am trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$10 02)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002 ="Peformer1",Sheet1!$R$3:$R$1002,Sheet2!$R$3:$R$10 02,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF across multiple worksheets
Hi Bob. I'm having a hard time using your example to fit my situation. The
sheet names I want it to check are 'Jan 06', 'Feb 06', 'Mar 06'... ending in 'Dec 06' (not including the '). I just want it to check one cell on these sheets, B42, to see if it matches the content of a cell on my current sheet ('Summary'), A3. Oh, and I'm trying to do COUNTIF instead of simply IF. Can you help? Thanks! "Bob Phillips" wrote: Just use =SUMPRODUCT(SUMIF(INDIRECT("1392"&ROW(INDIRECT("1: 3"))-1&"!$H$3:$H$1002"),"P e rformer1",INDIRECT("1392"&ROW(INDIRECT("1:3"))-1&"!$S$3:$S$1002")))/ SUMPRODUCT(COUNTIF(INDIRECT("1392"&ROW(INDIRECT("1 :3"))-1"!$H$3:$H$1002"),"P erformer1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... Sorry - their actual names are 13920, 13921, and 13922 "Bob Phillips" wrote: Yes, I used the fact that each sheet started with Sheet and suffixed by 1,2 and 3 in the formula. What are yours called? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xvfcc1" wrote in message ... Still not working.....do I need to replace "Sheet" with an actual sheet name? Sorry for being dense. I replaced Performer1 with the actual value in the cell - which is the person's name. "xvfcc1" wrote: I have a workbook that contains 3 worksheets, one for each vendor site. I am trying to average delivery time (column R) across all 3 sites based on which performer handled the request (column H). Is this possible? I am writing a formula that looks like this: =AVERAGE(IF(Sheet1:Sheet3!$H$3:$H$1002="Performer1 ",Sheet1:Sheet3!$S$3:$S$10 02)) However, this is not working. I also have tried this: =AVERAGE(IF(Sheet1!$H$3:$H$1002,Sheet2!$H$3:$H$3:$ H$1002,Sheet3!$H$3:$H$1002 ="Peformer1",Sheet1!$R$3:$R$1002,Sheet2!$R$3:$R$10 02,Sheet3$R$3:$R$1002)) This is not working either. Is this something that can be done or should I just do a statistical summary sheet and run the formulas from there? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
Line chart from multiple worksheets | Charts and Charting in Excel | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Update multiple worksheets | Excel Discussion (Misc queries) | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |