SUMIFS in Smartsheet
Hi all,
I'm trying to use SUMIFS in Smartsheet to sum actual hours for individuals between two dates. I'm using two sheets, one is the front facing table and the other is the raw data. I've tried this forumla which worked in excel, but won't work in smartsheet?
=SUMIFS(sum_range, individual_range, criteria1, date_range, ">=" & D3, date_range, "<=" & E3)
Could you help please?
sum_range
: The range of cells containing the actual hoursindividual_range
: The range of cells that you want to evaluate againstcriteria1
criteria1
: The name of the individual you want to sum the actual hours for.date_range
: The range of cells containing the dates. D3: The cell reference containing the starting date of the date range you want to include in the sum.
 E3: The cell reference containing the ending date of the date range you want to include in the sum.
Best Answers

Ok let's say on your data sheet you have a column called Actual Hours, a column called Individuals, and a Date column. On your frontfacing sheet you have an Individual column, a Start Date, and End Date. In your frontfacing sheet, try this, using the Smartsheet prompts to reference the data sheet:
=SUMIFS({Data Sheet Actual Hours Range}, {Data Sheet Individual Range}, Individual@row, {Data Sheet Date Range}, >= [Start Date]@row, {Data Sheet Date Range}, <= [End Date]@row)
(The ranges in italics need to be created as you're writing the formula.)
By using the @row functionality, this formula will work on every row without being changed.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

That's worked thank you both! @Genevieve P. @Jeff Reisman
Is it possible to reference it to two cells rather than having to put the start and end date to each row for my table?
Dummy data below, but left of the planned hours I have the individuals.
Answers

Ok let's say on your data sheet you have a column called Actual Hours, a column called Individuals, and a Date column. On your frontfacing sheet you have an Individual column, a Start Date, and End Date. In your frontfacing sheet, try this, using the Smartsheet prompts to reference the data sheet:
=SUMIFS({Data Sheet Actual Hours Range}, {Data Sheet Individual Range}, Individual@row, {Data Sheet Date Range}, >= [Start Date]@row, {Data Sheet Date Range}, <= [End Date]@row)
(The ranges in italics need to be created as you're writing the formula.)
By using the @row functionality, this formula will work on every row without being changed.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Hi @Jeff Reisman ,
Thanks so much for the quick response.
I tried the formula but unfortunately it doesn't sum the hours up.
Formula looks like this:
=SUMIFS({Copy of Test Sheet Range 1}, {Copy of Test Sheet Range 2}, Individual@row, {Copy of Test Sheet Range 3}, >=[Start Date]@row, {Copy of Test Sheet Range 3}, <=[End Date]@row)
Back end date:
Front end date:
My end goal is to have a table like this, and when I change the start date and end date, the hours used column updates.

@Paul Newcome @Genevieve P. are you able to help with the above as well please?

Hi @Jez Akali
Your formula is structured correctly! The 0 could be returned because the formula is unable to find matching rows, either because of the ranges selected or how the data is formatted.
Can you try to use COUNTIFS with the same criteria (removing the first range) to make sure it's finding the correct rows?
=COUNTIFS({Copy of Test Sheet Range 2}, Individual@row, {Copy of Test Sheet Range 3}, >=[Start Date]@row, {Copy of Test Sheet Range 3}, <=[End Date]@row)
Scenario 1  COUNTIFS is correct:
If this brings back the correct number, then the problem lies within {Copy of Test Sheet Range 1}
 Double check you've selected the correct column as "Range 1" by rightclicking on the range and selecting "Edit Reference"
 Check that the values in {Range 1} can be added together (that they're seen as numerical. You can do this by testing SUM without any criteria, like this. =SUM({Copy of Test Sheet Range 1})
Scenario 2  COUNTIFS is not correct:
If the COUNTIFS also returns 0, then the problem is with the criteria.
 Make sure that all of your Date vales are in Date type of Columns (Start Date, End Date, and the {Range 3} date column in your source sheet)
 If the Individual column is a Primary Column, try using @cell = Individual@row, like so: {Copy of Test Sheet Range 2}, @cell = Individual@row,
 Check that {Range 2} and {Range 3} are pointing at the correct columns, and the entire column
Let us know what these steps have uncovered and we'll be happy to help further!
Cheers,
Genevieve

That's worked thank you both! @Genevieve P. @Jeff Reisman
Is it possible to reference it to two cells rather than having to put the start and end date to each row for my table?
Dummy data below, but left of the planned hours I have the individuals.

Hi @Jez Akali
Yes, no problem! You can reference two date cells. This limits you from making it a columns formula, but you can dragfill down the formula instead.
For example, if your Start Date date is on row 2, and your End Date date is on row 4, you would want to add the ROW Number as a reference after the Column Reference:
=COUNTIFS({Copy of Test Sheet Range 2}, Individual@row, {Copy of Test Sheet Range 3}, >=[Date Column]$2, {Copy of Test Sheet Range 3}, <=[Date Column]$4)
See: Create a Cell or Column Reference in a Formula
Cheers,
Genevieve

Amazing, thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!