Sumifs formula circular reference error


So the first 36 rows on the sheet are a summary of the data below.

I want to capture the sum of values that meet a specific criteria.

=Sumifs([Value]:[Value],[Type]:[Type],="Type 1", [Category]:[Catergory],="Cat A"

but it's either blocked or the circular ref is poping up.

It's also putting the "Blocked" in other cells" - I don't know why.

Best Answer


  • John_Foster
    John_Foster ✭✭✭✭✭✭

    Hi @Voyage1,

    Without seeing the sheet I cannot be certain, but I would assume this is because the ranges specified are the ranges of the whole column which include the summary data in the cells at the top of the sheet.

    Are you able to include some screenshots of the sheet and the formula?


  • Voyage1
    Voyage1 ✭✭

    The specified range is the whole column which includes the Summary rows. Is there a way to only reference the rows greater than 36 ?

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @Voyage1 Not without editing your range. What you could do instead is to build an actual summary sheet that references your current sheet, or use the summary tab for summaries.

  • Voyage1
    Voyage1 ✭✭

    so new issue.

    The formula works in the summary field. but as soon as I try this it all turns blocked.

    First pic is the Sheet Summary with the formulas working.

    The second pic is putting an = to reference the Sheet Summary

    The third is what happens after:

    no idea why this is happening.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!