SUMIFS including date range in criteria

edited 12/09/19 in Smartsheet Basics

Hi There,

I wish to sum a column, providing multiple conditions are met. i come unstuck when one of those conditions is a date range.

i need to total doors due out this week and next week and the following week separately providing that they are ready to pick.

this is the formula that i have used so far which isnt giving me the correct total

=SUMIFS([# of Doors]:[# of Doors], Stage:Stage, "Picking", [Due Date]:[Due Date], >[Date Field]1, [Due Date]:[Due Date], <[Date Field]11)

please help.






  • Hi Blair,

    I think you're wanting to sum cells that meet one of your two date criteria, as well as your Stage criteria.

    Try this:

    =SUMIFS([# of Doors]:[# of Doors], Stage:Stage, "Picking", [Due Date]:[Due Date], OR([Due Date]:[Due Date] > [Date Field]1, [Due Date]:[Due Date] < [Date Field]11))

    This will get you results if one or the other date condition is met. 

    If you're still not getting a correct sum, please let me know more on the value that you're getting versus the value that you're expecting.


  • Hi Shaine,

    still getting an incorrect result on the above.

    i am actually trying to measure a series of data that falls within a specific date range and i fear that my greater than OR less than should be AND.


    is this possible and if so how to achieve this???






  • Hi Blair,

    Smartsheet has an AND function. Using the formula from my previous comment, try switching OR out for AND.

    More info on AND is available in the help center:

    If you're still not getting the correct result, let me know what number the formula is returning versus what number you're expecting.

  • Blair—another thought on this. 

    I think you need to be using COUNTIF or COUNTIFS instead of SUMIF.

    I think that the @cell parameter needs to be passed through your formulas as well:

    =COUNTIFS(Stage:Stage, "Picking", [Due Date]:[Due Date], OR(@cell> [Date Field]1, @cell < [Date Field]11))

    Let me know if that does the trick.