I am new to Smartsheet and finding some of the formulas a challenge

edited 06/20/22 in Formulas and Functions

"I am working on a sheet that I need to sum Line amount column if the dates are between a range of 12/15/21 and 5/31/22...

This is my formula

=SUMIF([Request Date1]:[Request Date]775, {Date Range},> DATE(2021, 12, 15), {Date Range}, <= DATE(2022, 5, 31), [Line Amount]1:[Line Amount]775)

what is wrong with my formula that it will not work?"


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Tammy.Burchill

    There are a couple of inconsistencies in your formula. I will assume that your [Request Date] column is the same as the {Date Range} that your reference. If this is not true, please correct me (screenshots are always helpful - remove sensitive data first).

    Assuming the Date ranges are the same thing - curly brackets indicate that the data is coming from a different sheet than where you are placing the formula. In smartsheet a different sheet is called a cross sheet and the curly bracket ranges are called cross sheet references. Assuming you are on the same sheet, you don't need these.

    The SUMIF function can only be used when you only have one criteria. With two dates, you have more than one criteria. The SUMIFS function can be used with any number of criteria, including if you have only one. Because the SUMIFS formula is more versatile, my personal preference is to only use the SUMIFS function.

    I also will assume that you want your entire column to always be summed, not just row1 to row775 if more rows are added. To do this, you reference the column name without row numbers

    =SUMIFS([Line Amount]:[Line Amount], [Request Date1]:[Request Date], > DATE(2021, 12, 15), [Request Date1]:[Request Date], <= DATE(2022, 5, 31))

    Is it intentional that you are referencing two different Date columns? Depending on how the data appears in the rows for these columns, the summed result might not be as you are expecting.

    If the formula does not work, please (1)copy the formula exactly as it appears in your sheet (2) let me know if you received unexpected results, or an error (exactly what error?). And again, a screenshot will help troubleshoot if you have errors or unexpected results.

    Let me know if this works for you


  • Tammy.Burchill
    edited 06/20/22

    @Kelly Moore

    Thank you I will try this and let you know...

  • @Kelly Moore

    Well it did not work gave me an error #UNPARSEABLE... below is a screen shot of the data and summary

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Tammy

    Look at your formula - with the colored column names. Are they colored? I had inquired about the two date columns you referenced but I only see one date column.

    try this

    =SUMIFS([Line Amount]:[Line Amount], [Request Date]:[Request Date], > DATE(2021, 12, 15), [Request Date]:[Request Date], <= DATE(2022, 5, 31))

  • Hi @Kelly Moore

    They are not colored ... I do not have 2 date columns

    I have tried this and now my answer to the formula is $0

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Tammy

    I added the @cell designation

    =SUMIFS([Line Amount]:[Line Amount], [Request Date]:[Request Date], @cell > DATE(2021, 12, 15), [Request Date]:[Request Date], @cell <= DATE(2022, 5, 31))

    This works in my sheet


  • @Kelly Moore

    I am now getting an answer of $0

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!