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

Tammy.Burchill

"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

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

Kelly

Tammy.Burchill

@Kelly Moore

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

Tammy.Burchill

@Kelly Moore

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

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))

Tammy.Burchill

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

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

Tammy.Burchill

@Kelly Moore

I am now getting an answer of $0