Share via

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

Anonymous
2024-03-20T08:51:34+00:00

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.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2024-03-20T10:14:59+00:00

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.

Was this answer helpful?

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-20T09:44:58+00:00

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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-03-20T09:36:59+00:00

    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.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-03-20T09:15:42+00:00

    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

    Was this answer helpful?

    0 comments