Sumifs formula circular reference error

Voyage1

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.

Find more posts tagged with

Accepted answers

Eric Law

@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.

All comments

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?

John

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

@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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.