Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SUMIFS with a date range criteria?

From a project sheet (with additional columns added) I'm trying to tally up the number of days by resource assigned to each quarter. So I want to specify a Q3 date range between 6/1/15 and 8/31/15, for example, as one of the SUMIFS criteria so that I only toal the number of days during that time frame. 

 

Something like so: 

 

=SUMIFS(Days1:Days47, ResourceName1:ResourceName47, "Name", FinishDate1:FinishDate47, [DATE RANGE?])

 

I found a post online with an equation that does this in Excel:

=SUMIFS(B2:B8,A2:A8,">=2014-03-04",A2:A8,"<=2014-05-10"

 

Is there something similar in Smart Sheet? 

 

Much thanks!

Tags:

Comments

  • Travis
    Travis Employee
    edited 08/24/15

    SUMIFS will not work with a date range but try this workaround:

     

    -Add a Checkbox column to your sheet 

    -Build a formula in the Checkbox column that will check the box if the corresponding Finish Date is within your set range

    -Use the checkbox column as a parameter in your SUMIFS

     

    The Checkbox column formula cant have dates added directly to it but it can reference cells containing your date range. 

     

    Example (formula located in CB column row 10):

    Cell [Finish Date]1 contains the start of the range (6/1/15)

    Cell [Finish Date]2 contains the end of the range (8/31/15)

     

    =IF(AND([Finish Date]10 > [Finish Date]1, [Finish Date]10 < [Finish Date]2), 1)

     

     

  • Is this the only way to sum data to give Q3 reports, like the OP requests, or monthly reports as I'm interested in?

     

    I need to sum data by month from a start date column.  Currently I have a secondary column with the month typed out and SUMIF based on month.  It would be great if there was a consolidated formula to work this magic. 

  • Travis
    Travis Employee

    Thane, SUMIF formulas wont be able to determine the months of a range of dates. The best option is to have a second column with a formula that shows the month of the corresponding date, then use a SUMIF on the month column. The month column can be hidden from your sheet once the formula is added.

  • Joe V
    Joe V
    edited 03/27/18

    I was looking for an answer to this and couldn't find one, but I happened upon this solution to this question... here's an example of setting up a dynamic data range within a SUMIFS formula:

     

    =SUMIFS({Range 1}, {Range 2}, "China", {Range 3}, >(TODAY() - 200))

     

    Range 1: the value range to be summed

    Range 2: a pretty common criteria range, which here must equal "China"

    Range 3: date range syntax that will filter out results older than 200 days.

     

    cheers

  • Lachlan Stead
    Lachlan Stead ✭✭✭✭✭✭

    Any reason why the above shouldn't work for me? I used the exact same formula expect referenced a different sheet... would this have anything to do with it?

This discussion has been closed.