Sum Hours in a column by row name

Formula>> =SUMIF([Assigned To]:[Assigned To], [Assigned To]@row, [Hours Testing]:[Hours Testing])

Formula works if only on a single row. eg will produce the sum 3 next to "Armida"

When i copy the formula it reads Circular Reference.

*Goal is NOT to type name in formula "Armida". I'd like it to reference the row Assigned To - so if that testing user changes, they simple update the contact name "assigned to" and the formula works.

Top Section is a list of people and their role.

Bottom section i have a list of UAT scripts - then "Assigned To" "Hours Testing"

I'm trying in the top section to sum hours testing with the associated name in that row.

Find more posts tagged with

Formulas

Accepted answers

Heather Duff

Hi @Angela.R ,

You're getting a circular reference because you're searching for names in the Assigned To column. When SS searches for rows that are assigned to Armida, it recognizes that row 1 is also assigned to Armida, so it tries to add the current cell to the other rows that meet that criteria.

The best way to do this would probably be to sum the data on a separate sheet. It's not ideal, but would be the best way to compile the data. There are other ways of doing this (sheet summary tab, moving the @row reference to a different column, etc.), but may be a little more involved.

Hope this helps!

Best,

Heather

Paul Newcome

The reason for the Circular Reference error is because you have the formula referencing the column that it is in. Smartsheet will only allow one instance of this.

There are a number of ways to work around this.

You could (as @Heather D suggested) pull your metrics onto another sheet.

You could move these calculations to a column that is not being referenced by the formula.

You could use Sheet Summary fields.

All comments

Heather Duff

Hi @Angela.R ,

Hope this helps!

Best,

Heather

Paul Newcome

The reason for the Circular Reference error is because you have the formula referencing the column that it is in. Smartsheet will only allow one instance of this.

There are a number of ways to work around this.

You could (as @Heather D suggested) pull your metrics onto another sheet.

You could move these calculations to a column that is not being referenced by the formula.

You could use Sheet Summary fields.

Angela.R

Okay, i've moved the formula to another column in the sheet " =SUMIF([Assigned To]:[Assigned To], =[Assigned To]@row, [Hours Testing]:[Hours Testing])"

Seems to be working.

I had it placed in my top section "Hours Testing" and moved to another column, and now it appears to total the sum hours of that @row user. If someone changes the contact filed "Assigned To" it will auto populate for that user name.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.