SUMIF statement based on dates

dchapmandchapman
edited 12/09/19 in Formulas and Functions
09/25/18 Edited 12/09/19

I would like to create a SUMIF equation to only sum data (column F) based on a date range (column B).  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

Popular Tags:

Comments

  • Mike WildayMike 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 WilliamsJ. Craig Williams Top Contributor

    "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 WildayMike 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? 

  • edited 07/24/19

    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!

  • 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 WilliamsJ. Craig Williams Top Contributor

    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

     

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

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    You are welcome

    Craig

  • 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 NewcomePaul 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"?

Sign In or Register to comment.