SUMIFS with a date range criteria?

Chris Sullivan

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!

Travis

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)

 

 

Thane Knutson

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

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

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

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?