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?

=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?

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?

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?

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?

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?

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?

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?

Do you want to test if B42 on *ANY* sheet = Summary A3?

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(ROW
(INDIRECT("1:12"))*30,"mmm")&" 06'!B42"),A3))0

Will return either TRUE or FALSE

Biff

"Treesy" wrote in message
...
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?

Thanks for the reply. The formula worked enough not to give me an error but
the result was TRUE instead of counting the instances. On each individual
sheet, I have a formula that calculates which item has the highest sales for
that month. On my summary sheet, I have a list of the items and I want it to
look at each month's sheet and if that item had the highest sales, I want it
to count. Any ideas how I can do that?

Thank you.

"T. Valko" wrote:

Do you want to test if B42 on *ANY* sheet = Summary A3?

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(ROW
(INDIRECT("1:12"))*30,"mmm")&" 06'!B42"),A3))0

Will return either TRUE or FALSE

Biff

"Treesy" wrote in message
...
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?

Hmmm... i think I just fixed it. I deleted the 0 on the end and it suggested
I also delete the , making the formula (INDIRECT("1:12"))*30,"mmm")&"
06'!B42"),A3)) and that seemed to work!! :)

Thanks.

"Treesy" wrote:

Thanks for the reply. The formula worked enough not to give me an error but
the result was TRUE instead of counting the instances. On each individual
sheet, I have a formula that calculates which item has the highest sales for
that month. On my summary sheet, I have a list of the items and I want it to
look at each month's sheet and if that item had the highest sales, I want it
to count. Any ideas how I can do that?

Thank you.

"T. Valko" wrote:

Do you want to test if B42 on *ANY* sheet = Summary A3?

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(ROW
(INDIRECT("1:12"))*30,"mmm")&" 06'!B42"),A3))0

Will return either TRUE or FALSE

Biff

"Treesy" wrote in message
...
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?

I wasn't sure if you wanted a total count or whether you just wanted to know
if there was at least 1 match.

Biff

"Treesy" wrote in message
...
Hmmm... i think I just fixed it. I deleted the 0 on the end and it
suggested
I also delete the , making the formula (INDIRECT("1:12"))*30,"mmm")&"
06'!B42"),A3)) and that seemed to work!! :)

Thanks.

"Treesy" wrote:

Thanks for the reply. The formula worked enough not to give me an error
but
the result was TRUE instead of counting the instances. On each
individual
sheet, I have a formula that calculates which item has the highest sales
for
that month. On my summary sheet, I have a list of the items and I want
it to
look at each month's sheet and if that item had the highest sales, I want
it
to count. Any ideas how I can do that?

Thank you.

"T. Valko" wrote:

Do you want to test if B42 on *ANY* sheet = Summary A3?

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(ROW
(INDIRECT("1:12"))*30,"mmm")&" 06'!B42"),A3))0

Will return either TRUE or FALSE

Biff

"Treesy" wrote in message
...
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?