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
-
@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.
Answers
-
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?
John
-
The specified range is the whole column which includes the Summary rows. Is there a way to only reference the rows greater than 36 ?
-
@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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!