Averageif over multiple sheets

[mamachrissy1028]

I am trying to average elapsed time for various tasks across several pages. The pages are named after the date (2-27, 3-2,3-3, ect) I understand that Averageif is not able function in this example so i was trying to use a formula i found in another thread but i keep getting a reference error and i am not sure why. My task names are in column A and my elapsed time is in column E. My original formula for a single page is

Please Login or Register  to view this content.

. My borrowed formula is

Please Login or Register  to view this content.

I tried narrowing the pages down to just 2 of the sheets but still got the error. Please help i don't know where i am going wrong! Thank you!

[mikeTRON]

I think it is FAR easier to bring all the data to ONE tab then do your averafeifs on ONE data set.

[FDibbins]

Here is a short explanation on how to do this.

1. put all your sheet names in a list somewhere and give that list a named range
2. =SUMPRODUCT(SUMIF(INDIRECT("'"&range-name&"'!T6:T1000"),A1,INDIRECT("'"&range-name&"'!M6:M1000")))

A1 = the search criteria

I agree with Mike that is is always easier to have your data all on 1 sheet, but I also understand that this is not always possible

[mamachrissy1028]

You are correct and i am not able to list all the data on 1 tab, if i could i certainly would. However, I am still getting a reference error and i don't know what is going wrong. I have checked all my cells in the tabs i am referencing and none of them contain any errors. i have created a named range as you indicated. Could this have anything to do with they way the tabs are named? I have attached a spreadsheet to maybe give you a better idea of what is going wrong. Thank you sooo much for all your help!TEST.TASK.xlsx

[XOR LX]

Hi.

1) Your Named Range list cannot contain the names of any sheets which do not exist within your workbook. Your current definition for TABS is:

=Sheet1!$A$2:$A$24

yet only A2:A7 contain actual values; the rest are blank.

2) Your entries within that range must match precisely the names on the tabs. Currently you have actual dates in your list yet text representations of dates on the actual tab names. If you want to enter e.g. "2-27" into a cell without Excel converting it to a date, then precede the entry with a single apostrophe, or else pre-format the cell as Text.

Regards

[mamachrissy1028]

thank you soooooo much! i had wondered if it was a formatting problem.

[XOR LX]

You're welcome!