Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

Wherever you have a range like S27:S32, or Q27:Q32, in your formula, you need to put 'Sheet 2'! in front of it - the apostrophes are needed if your sheet name contains spaces. I think this means that you need to do it 10 times within your first formula.

Hope this helps.

Pete

Thanks for the response. I am aware that the cell ranges should be preceded with, in my case, 'ACCESS DATA'!. I did have the sheet names with apostrophes in the formula as it was on my Excel sheet, but because that formula doesn't work due to it being an AVERAGEIFS across several pages, I deleted it from Excel. I didn't think it was necesary to edit the non-cross-tab formula before posting here.

For sake of completeness, here's the full formula that is not working:

=SUM((AVERAGEIFS('ACCESS DATA'!S27:S32,'ACCESS DATA'!S27:S32,">"&TODAY()-90,'ACCESS DATA'!P27:P32,"license*",'ACCESS DATA'!Q27:Q32,"mailed*",'ACCESS DATA'!S27:S32,">"&TODAY()-90))-(AVERAGEIFS('ACCESS DATA'!N27:'ACCESS DATA'!N32,'ACCESS DATA'!N27:'ACCESS DATA'!N32,">"&TODAY()-90,'ACCESS DATA'!P27:'ACCESS DATA'!P32,"license*",'ACCESS DATA'!Q27:'ACCESS DATA'!Q32,"mailed*",'ACCESS DATA'!S27:'ACCESS DATA'!S32,">"&TODAY()-90)))

Thanks very much for any guidance on this!

Well, you only need to put the sheet name once before the range - you do not need:

'ACCESS DATA'!N27:'ACCESS DATA'!N32

for example: instead it should be this:

'ACCESS DATA'!N27:N32

This applies to the second part of your formula.

Hope this helps.

Pete

Thanks. However, I did have the format in my Excel sheet correct when I had it in there, and so that's really not the problem. The problem is that AVERAGEIFS not working between 2 Excel sheets, and so I'm trying to find a solution to that.

Any advice is greatly appreciated.

Thanks.

AVERAGEIFS should work across another sheet, so what do you mean by "not working"?

Is there any chance that you can post an example workbook ? The FAQ gives details of how to do this.

Pete

Thanks for the response. I get the result #VALUE! when I use the formula with values from another sheet. I think this should be replicatable. I think this is an established issue, and I did find some attempts, and some examples of, solutions in web searches. Unfortunately, none of the proposed solutions worked for me. Interestingly, a recent Google search pulled up a discussion on a MS website. The discussion subject was "BUG in Excel 2010 SUMIF & SUMIFS functions [EDIT: also AVERAGEIF & -IFS]" at http://social.technet.microsoft.com/Forums/en/excel/thread/cda9fef8-8ad4-4d56-8939-49b1ae50c4e7/ , so there are others who are having the same issues.

Unfortunately it seems that no one could help out with this. But, I was able to come up with a solution myself, and so in case anyone needs any help finding a solution to the IFS-across-sheets Excel bug - Averageifs, sumifs, etc - here's the solution I came up with. It's pretty serious - so of course I appreciate any suggestions or pointers on simplifying it.

=(SUMPRODUCT(--(ISNUMBER(SEARCH("License",'ACCESS DATA'!$H$2:$H$25000))),(ISNUMBER(SEARCH("Mailed",'ACCESS DATA'!$J$2:$J$25000)))*('ACCESS DATA'!$G$2:$G$25000>=TODAY()-90)*('ACCESS DATA'!$K$2:$K$25000)))/COUNTIFS('ACCESS DATA'!$H$2:$H25000,"License*",'ACCESS DATA'!$J2:$J25000,"Mailed*",'ACCESS DATA'!$K2:$K25000, ">="&TODAY()-90)-(SUMPRODUCT(--(ISNUMBER(SEARCH("License",'ACCESS DATA'!$H$2:$H$25000))),(ISNUMBER(SEARCH("Mailed",'ACCESS DATA'!$J$2:$J$25000)))*('ACCESS DATA'!$K$2:$K$25000>=TODAY()-90)*('ACCESS DATA'!$G$2:$G$25000)))/COUNTIFS('ACCESS DATA'!$H2:$H25000,"License*",'ACCESS DATA'!$J2:$J25000,"Mailed*",'ACCESS DATA'!$K2:$K25000, ">="&TODAY()-90)

Yeah I see that now. I think my eyes were getting crossed in trying to find a solution.

I appreciate your formula, it works great. Thanks a ton!