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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!