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

Copper Contributor

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:


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



Do you have any circular references anywhere?




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



How can I get that?

My B column is calculated like this:



How can I identify the circular references?


I did test for Circular references but it seems greyed out in that sheet:


@Detlef_Lewin @JosWoolley 

best response confirmed by VojtechSima (Copper Contributor)
1 best response

Accepted Solutions
best response confirmed by VojtechSima (Copper Contributor)