SUMIFS in Smartsheet

Jez Akali

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 hours
• individual_range: The range of cells that you want to evaluate against criteria1
• 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.

Jeff Reisman

@Jez Akali

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 front-facing sheet you have an Individual column, a Start Date, and End Date. In your front-facing 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.

Jez Akali

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.

Jeff Reisman

@Jez Akali

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 front-facing sheet you have an Individual column, a Start Date, and End Date. In your front-facing 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.

Jez Akali

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.

Jez Akali

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

Genevieve P.

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 right-clicking 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

Jez Akali

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.

Genevieve P.

Hi @Jez Akali

Yes, no problem! You can reference two date cells. This limits you from making it a columns formula, but you can drag-fill 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

Jez Akali

Amazing, thank you!