SUMIFS between specific cells

Hi!

I want to sum the values in a column only if the values are between cells 2 to 729 and only if "Nina Gallagher" is in the "Who" column

I've gotten this far --

=SUMIFS([10/5/20]:[10/5/20], Who:Who, ="Nina Gallagher")

When I try this, I get #INCORRECT ARGUMENT --

=SUMIFS([10/5/20]2:[10/5/20]729, Who:Who, ="Nina Gallagher")'

The row in pink in the photo below is where the formulas are. I'd appreciate any help!

Screen Shot 2020-11-18 at 7.13.27 PM.png

Find more posts tagged with

Accepted answers

All comments

Mike Wilday

Have you thought about using the Sheet Summary cells to do these calculations? Then you won't have to use specific ranges. This could get messy when new rows are added to accommodate other individuals.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary

clairee42221

@Mike Wilday Unfortunately, summary sheet won't work. We are using this for time tracking. This pink line at the bottom of the sheet gets pulled into Nina's timesheet (which is a report) and shows her how many hours she's worked that day.

Do you know how I can correct the formula above to only sum the values between specific cells?

Ian Smith 2017

Your ranges need to match, currently you are limiting the date range between 2 and 729, but leaving the who range open.

Try amending your formula to:

=SUMIFS([10/5/20]2:[10/5/20]729, Who2:Who729, ="Nina Gallagher")'

clairee42221

Thanks, @Ian Smith 2017 ! The error message that comes back when I add 2 and 729 to Who is #CIRCULAR REFERENCE. Any thoughts?

Mike Wilday

Circular reference means that you are including the formula in the sumifs range. Shorten the range to not include Nina's summary row. And make sure to do the same numbers for every other criteria range.

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--circular-reference

But couldn't you include the data in a sheet summary report?

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary-reports

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.