SOLVED

# SUM formula doesn't work (Summing range calculated by INDIRECT formula)

Hello guys,
I have dynamic list of SheetNames.

Then I have couple of columns where I for each sheet calculate certain formula with INDIRECT function inside of it to refer to a sheet.

Like this:

``=COUNTIF(INDIRECT(B2 &"!\$L\$2:\$L\$33"); "Something")``

The whole sheet look like this (sorry for the czech language but it's not relevant to the issue): Then I'd like to sum up every column, simply like this:

``=SUM(C2:C14)``

However, it returns 0. Even tho I formatted it as Number or I also tried to add VALUE formula etc, nothing really works and I have no clue.

Could someone help me how I can sum the columns?

6 Replies

# Re: SUM formula doesn't work (Summing range calculated by INDIRECT formula)

Hi,

Do you have any circular references anywhere?

Regards

# Re: SUM formula doesn't work (Summing range calculated by INDIRECT formula)

I'm taking a wild guess: There is a circular reference in the worksheet.

# Re: SUM formula doesn't work (Summing range calculated by INDIRECT formula)

How can I get that?

My B column is calculated like this:

``=INDEX(SheetNames;A2)``

How can I identify the circular references?

# Re: SUM formula doesn't work (Summing range calculated by INDIRECT formula)

Hello, as I replied to @JosWoolley .

# Re: SUM formula doesn't work (Summing range calculated by INDIRECT formula)

I did test for Circular references but it seems greyed out in that sheet: best response confirmed by VojtechSima (Occasional Contributor)
Solution

# Re: SUM formula doesn't work (Summing range calculated by INDIRECT formula)

So I fixed by enabling the Iterative Calculations: 