SUMIF statement based on dates

dchapman

I would like to create a SUMIF equation to only sum data (column F) based on a date range (column .  It works in excel with the following equation for reference.

=SUMIF($B$9:$B$2000, ">=9/1/2018", $F$9:$F$2000)-SUMIF($B$9:$B$2000, ">=9/30/2018", $F$9:$F$2000)

 

However, when I import it to smartsheets, I get an error. 

 

My best attempt at the new equation is (also creating an error):

=SUMIF(Date:Date, AND(@cell >= DATE(2018, 9, 22), @cell < DATE(2018, 9, 28))), Onboarding:Onboarding)

 

Could someone help me with how the equation should be formatted?

Thanks,

DeAnna

Mike Wilday

Sumif can't use AND statements. Just rewrite it as a SUMIFS which allows you to have multiple criterion. Try this: 

=SUMIFS(Onboarding:Onboarding, Date:Date, >=DATE(2018, 9, 22), Date:Date, <DATE(2018, 9, 28))

https://help.smartsheet.com/function/sumifs

J. Craig Williams

"SUMIF can't use AND" is incorrect.

I don't use SUMIF (preferring always to use SUMIFS even for 1 criterion situations)

Here is the use in SUMIFS

=SUMIFS(Onboarding:Onboarding, Date:Date, AND(@cell >= DATE(2018, 9, 22), @cell < DATE(2018, 9, 28)))

Craig

Mike Wilday

Really? I have tried using ANDS in Countifs and Sumifs multiple times. That is intriguing. Did they recently fix it? Or does it not work in countifs but does work in sumifs? 

Becky Swanwick

I have a similar query to the one above and had got as far as figuring I needed a SUMIFS formula. However, I'm not quite following your solution, Craig -- to what does @cell refer? I guess I may be complicating things because mine involves referring to another sheet, so I need to introduce ranges -- but essentially it's the same query as the original poster's. Any help you can give would be much appreciated!

J. Craig Williams

@cell reference

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Becky Swanwick

Thank you. I think my difficulty is now that I want to refer to two date columns. So I want cells to sum where the date in the start column is after a particular date but the date in the finish column is before a particular date. Does that make sense?

J. Craig Williams

Something like this should work

=SUMIFS({range to sum}, {date range 1}, @cell >= DATE(2018, 9, 22), {date range 2}, @cell < DATE(2018, 9, 28))

This can be on the same Sheet or (as shown here) on a different Sheet with x-references, as long as the 3 ranges are the same size.

You don't want to use the AND because the range for each criterion is different (location, not size)

Craig

 

Becky Swanwick

Hi Craig, a belated thanks for this -- very helpful!

J. Craig Williams

You are welcome

Craig

SophiaJKhan

Hi Craig, I may be late to this discussion, but I am struggling with two things, one is the Sumifs with criteria referencing another sheet, as well as the @cell feature.

I have 2 sheets for PTO tracking accrual. The first is a table with every date of the year. In the corresponding line I am trying to use the following formula:

=SUMIFS({Approved PTO Hours}, {PTO Start Date}, Date2, {Employee Name}, "NAME")

So basically I am taking the range from another sheet which has all approved PTO stored in it, then criteria 1 is matching the date column in the Approved PTO to the date on the respective line, criteria 2 validates the employee name matches. This way if there is any approved PTO for a respective date and employee on the Approved PTO sheet, it should pull in on the line with the date and employee on the master data file.

What am I doing wrong with the formula?

THANK YOU!

Paul Newcome

@SophiaJKhan You formula appears to be correct. How are you creating your cross sheet references? Have you double checked to ensure columns and data are of the correct type? Are you able to provide screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy data"?