# SUMIFS between specific cells

Options

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!

• ✭✭✭✭✭✭
Options

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.

• Options

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

• edited 11/19/20
Options

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

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

• Options

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

• ✭✭✭✭✭✭
Options

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.

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!