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

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

Hello all,

Here's my formula, I'll explain below what I'm trying to accomplish since I'm hoping there's a viable way to do this. The stumbling block now is that the data (and which is not indicated in the formula below!!) is on a different sheet, and so this formula does not work to retrieve that data.

Basically, I want to get the average number of days it took to process a license over the last 90 days. The limiters are the dates (S, N), the word "license" with a wildcard (P), and the word "mailed", with a wildcard (Q). So, what I'm doing is below:

[Second part of the equation]:

I'm taking the 'average' of the "traffic inputted" (date) column (N) for today (minus) 90 where those rows contain cells with the term "mailed*" (column Q) and "license*" (column P) and have a "mailed date" (column S) of today-90.

[First part of the equation]

Sum aiside, I'm taking the average of the "mailed date" column (column S) for today-90, where those rows contain the term "mailed*" (column "Q") and "license*" (column "S"), and have a "mailed date" (column S) of today-90.

This combination will give me the average number of days over the last 90 days it took to complete license work - calculating from when the licenses were entered into the system until the time they were copleted.

Is there an equivalent formula that I can use to make this work accross sheets?

Thanks SO SO much for any help!!

2. ## Re: 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

3. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

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!

4. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

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

5. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

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.

Thanks.

6. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

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

7. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

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.

8. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

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.

9. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

Originally Posted by restivo
In this version you are repeating the same criterion twice in the same AVERAGEIFS function, should one of those refer to N27:N32? If so then it might be cleaner to use an "array formula" with AVERAGE function, you don't need to repeat any criteria and it won't have any of the problems you have with AVERAGEIFS, i.e.

confirmed with CTRL+SHIFT+ENTER

That will work, no problem, with the data on another sheet

10. ## Re: Alternative to *long* AVERAGEIFS formula, since all data is on a second Excel sheet?

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1