Simple SUM throws a #REF error - what am I doing wrong?

Question

I have very simple table looking like this:

Jahr	KW	Montag	Dienstag	Mittwoch	Donnerstag	Freitag	Samstag	Sonntag	Saldo
2024	5	0,0	0,0	0,0	8,0	8,0	0,0	0,0	#BEZUG!
2024	6	8,5	8,5	7,5	8,5	8,5	0,0	0,0	#BEZUG!

The formula in the last column is

=Summe([@Montag]:[@Sonntag])

Unfortunately Excel localises everything, but "Summe" is "SUM" and "#BEZUG" is "#REF". Ah, and "," is ".", so those are really numbers.

What I've tried:

• Adding the table name to the reference, i.e. SUMME(Table[@[Montag]:[Sonntag]]) - doesn't change anything
• replacing the data by plain numbers (it's formulas now) - doesn't change anything
• creating a new sheet and recreating the table and formula - doesn't change anything
• creating a new Excel document and recreating the table and formular - the problem is gone

Unfortunately, the remaining sheets constitute an external data source, so are not optional.

I would be very happy if someone has an idea what could be going on here.

Answer 1

I just got it. There was a table named "Summe" in one of the other sheets. That's also why Summe is not capitalised in the formula. So PEBKAC. Sorry for wasting your time, Snow.

Answer 2

You may upload file (Without sensitive information) in private message. It will help to test directly on it.

Answer 3

Thank you for looking at this so quickly!

Unfortunately, I can't really share an example - I've edited my post to show my attempts so far.

It does look like the other sheets create the problem somehow, but the data does arrive in this sheet and I don't understand how this affects a simple sum.

Answer 4

Here is my test.

=SUM(Table1[@[Montag]:[Sonntag]])

Table1 is the Tabel Name.

Could you share us a test file?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community