SUMIF statement based on dates

dchapman ✭✭
edited 12/09/19 in Formulas and Functions

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?





  • Mike Wilday
    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))

  • J. Craig Williams
    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)))


  • Mike Wilday
    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
    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 Williams
    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)



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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    You are welcome


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


  • Paul Newcome
    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"?

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!