IF across multiple worksheets

[Guest]

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?

 

[Bob Phillips]

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

 

[Guest]

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.

 

[Guest]

I am not getting this to work. I have additional data in column S - does this
make a difference?

 

[Bob Phillips]

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)

 

[Guest]

Sorry - their actual names are 13920, 13921, and 13922

 

[Bob Phillips]

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)

 

[T. Valko]

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

 

[Guest]

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.

 

[T. Valko]

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