SUMIFS and date

Windy

Hi All,

I am trying to reproduce this excel formula in smartsheet

=SUMIFS(Sheet2!C:C,Sheet2!A:A,"<="&A2,Sheet2!B:B,">="&A2)

I am trying to get a result equivalent to Column B in sheet 1 (see attached file) based on criteria in sheet 1 on column A from the data in sheet 2.

My formula in smartsheet is 

=SUMIFS({Sheet2 Column C Range 1}, {Sheet2 Column A Range 2}, <=Date1, {Sheet 2 Column B Range 3}, >=Date1)

Any help much appreciated

Bonus question...

How does one create incremental dates in a column?  With excel you can drag OR create a formula to add 1 to the cell above. I just get a 1 appended to the end of the date string (Column is in "date" format).  E.g. instead of 2/3/19 I get 1/3/191!

 

 

 

Paul Newcome

What steps are you taking for the cross sheet references in Smartsheet?

 

Also... The date thing should work. Are both columns formatted as date type columns? Exactly what formula are you trying to use to add 1 to the date?

Windy

Many thanks Paul.  Now all works - all because column wasn't set to date!

Paul Newcome

Happy to help.