Excel help -- averaging figures from different sheets

Factual Questions
1

I’ve been tracking monthly household spending in a single XL spreadsheet, with a different tab for every month. I want to compile a running average in different categories – groceries, medical, etc.

To average the C74 field from the sheets reflecting May through August (also the names of the sheets), I found this formula:

=AVERAGE(May:August!C74)

But I get an error message: Unknown range name: ‘MAY’.

What am I doing wrong?

2

Try this, insert single quotes

AVERAGE(‘May:August’!C74)

3

Nope:

Unresolved sheet name ‘May:August’.

4

The formula in OP works fine for me.

5

Me too. I assume the problem lies in the sheet names. If I deliberately screw up one of the month names Cheesesteak’s solution rectifies it.

6

I renamed them from “Sheet 1” to “May,” etc. So now the formula won’t work?

7

So when you renamed them did the formula change the names itself?

8

Nope, that’s not a problem for me either. If I rename a sheet, it automatically updates the name in the formula. As it happens, I did it exactly that way - I started off with them just called Sheet1, Sheet2 etc., then changed the names to months.

9

I didn’t have the formula when I renamed them. I just tried it for the first time this morning.

10

I did it both ways.

11

Have you checked very carefully for mundane errors - typos in the formula or sheet name? I don’t think sheet names accept blank spaces, so that shouldn’t be it.

ETA: they do. Something like a leading or trailing blank space int he sheet name?

12

It could be that the names are misspelled somewhere, either in the tabs or in the formula. Also be sure the tabs are sequential.

You can try to select the May cell then hold shift and select the August tab, that should autopopulate the formula for you.

13

You can have spaces in sheet names. That is how I changed May to check Cheesesteak’s solution.

14

Yes.

They are.

Don’t know if this matters, but … they’re left-to-right in reverse chronological order. “May” is May 2023, while “August” is August 2022.

15

No, the sheet names are just labels. It sums across all sheets between the two you specify, based on the ordering of the sheet tabs at the bottom. If you (for example) move the August sheet before the July sheet in the tabs at the bottom, the July sheet is excluded from the May:August! average.

Oh, hold on - you said they are in REVERSE chronological order?

16

Yes. I start a new sheet every month, and slide the rest to the right.

17

That still doesn’t appear to be the problem. If I attempt to sum across tabs but with the order of the first & last tab reversed, Excel automatically corrects the formula as enter it. So if I tried to type in the formula in OP when the leftmost tab is August and the rightmost tab is May, Excel corrects the formula to:

=AVERAGE(August:May!C74)

18

I still think you have a typo somewhere.

19

I don’t!

Here’s my formula: =AVERAGE(May:August!C74). And both May and August are spelled correctly in the sheet names.

I’ve tried single quotes, and reversing the months. It ain’t working.

Could the problem be the XL I’m using? It’s in Drive, and the spreadsheet name ends in .XLSX.

20

I have a spreadsheet stored in Google Drive with the same extension of .XLSX.

I have Excel 2016 installed on my laptop.

This formula works just fine in my spreadsheet: =AVERAGE(Sheet1:Sheet3!A1)