Average a cell in a column if matching a set of criteria

[Kosherboychief]

Hello! Thanks for attempting to help! :D
Alright, so I am fairly well versed in excel but this just confounds me!

First just let me describe my situation:
I am working across several sheets in a single book (5 to be exact) and am doing an averaging of call times so the top brass can just look at one sheet and see everyone’s averages. I had this done already with the formula =IF(SUM('WEEK 1:WEEK 5'!C6)=0, "", AVERAGE('WEEK 1:WEEK 5'!C6)) so that it would average but if there was nothing to average then that cell would remain a "" and therefore not be averaged in the other formulas for that sheet (on the averages sheet there are other formulas that would get a little skewed if a zero showed up and I didn't like the ref error or whatever it was).

But now they want the numbers to be ordered from greatest to least on the sheets being used to average, and separated into a few groups (groups I can do, and ordering I can do) however, this will mess up the averages when the cell it needs is in a different location.

So I need a formula, or formulas (I'll just hide them if it needs more than one cell, no big deal) that will only display a "" when no value is present, that will only average a cell from another sheet that’s on a row that has a certain value, let’s say value 7012 but will always be in the same column (let’s say it has to use a cell that =7012 in column A6:A15) but could be on any row in that column.

I can explain more if needed, or post a sample of what it looks like currently if needed.

Thanks so much for your help!

[MarvinP]

Hi,
I'd be working for putting all 5 data sheets into a single table and doing a pivot table on it. A sample would be needed to try my theory.

BTW - you might edit your title to the problem (as in formum rules). Something like Dashboard of 5 sheets ordered.

[shg]

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

[Haseeb Avarakkan]

Can you please attach a dummy file?

[Kosherboychief]

I just stuck some random numbers in, and don't worry about last call and first call columns, they don't need them averaged or sumed. And just pretend they are in a random order

[Haseeb Avarakkan]

See the attached.

[Kosherboychief]

Thanks so much Haseeb A, your solution fixed the totals!

However, the average formula does not work correctly because it also counts blank cells in the range, so it averages incorrectly.

=IFERROR(TOTALS!G8/SUM(COUNTIF(INDIRECT("'Week "&{1,2,3,4,5}&"'!A:A"),$A8)),"")

It comes back with =IFERROR(834/SUM({1,1,1,1,1}),””) when I evaluate it, which =834/5. It should be =834/4 since I don’t want the blank cells counted

I tried placing a few COUNTA’s in there but to no avail 

Help?

[Kosherboychief]

I have tried placing the COUNTA after the INDIRECT but that has not worked

[Haseeb Avarakkan]

Try this in Average C6, copy to other cells.

=IFERROR(TOTALS!C6/SUMPRODUCT(COUNTIFS(INDIRECT("'Week "&{1,2,3,4,5}&"'!A1:A1000"),$A6,INDIRECT("'Week "&{1,2,3,4,5}&"'!"&ADDRESS(1,COLUMN())&":"&ADDRESS(1000,COLUMN())),">0")),"")

[Kosherboychief]

Thanks so much Haseeb, your a real life saver!