Forum Discussion
Averaging the sum of a specific cell across multiple sheets
- Oct 01, 2023
Try
=AVERAGE('SHEET NAME 1:SHEET NAME 2'!AC25)
I.e. single quotes before the name of the first sheet and after the name of the last sheet, but not in between. And ! between the second single quotes and the cell address.
Try
=AVERAGE('SHEET NAME 1:SHEET NAME 2'!AC25)
I.e. single quotes before the name of the first sheet and after the name of the last sheet, but not in between. And ! between the second single quotes and the cell address.
HansVogelaar I have been trying to play around with this, to see if I can have the formula omit any pages that have a No listed in cell AC23, so that it automatically filters out the disqualified sheets.
=AVERAGEIF(AC23,"No",'SHEET NAME 1:SHEET NAME 2'!AC25)
Any chance you could explain where I'm going wrong with this one?
- HansVogelaarOct 03, 2023MVP
In the first place, you refer to AC23 on the active sheet, not on the list of sheets.
In the second place, unlike AVERAGE, the function AVERAGEIF does not support a multi-sheet range, at least not directly.
As a workaround, create a list of the sheet names from SHEET NAME 1 to SHEET NAME 2, and use formulas to retrieve the values of AC23 and AC25. You can then use AVERAGEIF:
- SeanHaggertyOct 06, 2023Copper Contributor
HansVogelaar Thanks for the explanation. Makes sense why it is not returning the value I want now.
I followed what you laid out, but the AVERAGEIF is presenting a #DIV/0! error. The values for AC23 and AC27 (had to add a couple cells, but its updated across all sheets) are returning correctly, so not sure what error I've made now.- HansVogelaarOct 07, 2023MVP
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?