AVERAGEIFS_based on another column

[chemhany]

I have this function to calculate the average based on another column for the workbook that contains several worksheets

=AVERAGEIF(INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!L7:M32"),A$1,INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32"))

How to use averageifs in case I want to calculate the average in D7:D32 for numbers >=0.8 and <=1.0 ?

I have used this function

=AVERAGEIFS(AVERAGEIF(INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!L7:M32"),A$1,INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32")),INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32"),">=0.8",INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32"),"<=1")

But there is an error ??????

Any idea

Thanks

 

[jasonb75]

You only need the averageifs function, trying to next both of them together will never work.

=AVERAGEIFS(INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32"),INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!L7:M32"),A$1,INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32"),">=0.8",INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32"),"<=1")

 

[chemhany]

I used that function but it gives #VALUE!

 

[jasonb75]

Is it just D7:D32 to average?

I missed something in your first formula that would cause it to not work correctly. Your criteria range is set to L7:M32 (2 columns wide) which means that the original formula using AVERAGEIF would have resized the average range to match and actually averaged D7:E32, not D7:D32 as required. If there are no numbers in E7:E32 then it will work correctly, if there are numbers then the results will be incorrect. For it to work you would need to change the ranges in the formula that say D7:D32 to D7:E32 (noting the previous point).

 

[chemhany]

Well, in this function
=AVERAGEIF(INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!L7:M32"),A$1,INDIRECT("'"&ROW(B5)-ROW(B$5)+1&"'!D7:D32"))
L and M columns are merged and actually there are numbers in E7:E32 and it working well as I have calculated manually to be sure. When I have set AVERAGEIF function, I wanted to calculate the average in D7:D32 if L7:M32 contains the word that matches A$1, although there are numbers in E7:E32.
Any way your help is working because when I changed from D7:D32 to D7:E32, the AVERAGEIFS works
That just to illustrate

Thanks

 

[jasonb75]

L and M columns are merged

If they are merged then only the left hand column would be seen to meet the criteria. Merging is best avoided as it can cause all manner of problems with formulas.
With the merging in mind, I would suggest changing the criteria range to L7:L32 and returning the other ranges to D7:D32 so that they are all single columns rather than including the columns that are not needed as previously suggested.

 

[chemhany]

Thanks so much ??