Help with using System Created date in a SUMIFS formula

Christina C

Hello!

I am attempting to create a formula in a metric sheet that simply adds a revenue column in another sheet with 2 criteria. 1st criteria is that the "Duration" column = "One Time", the second criteria is where the system generated creation date is greater then/equal to 1/1/2024.

I've tried several formulas and am getting either inparsable or incorrect arguement errors. I finally got the following formula to work but the value returned is incorrect as 0 because you see in my data that there are rows that meet my criteria and the value I expect to be returned is 6,670

=SUMIFS({2024 Admin Solutions Out of Scope Revenue Range 2}, {2024 Admin Solutions Out of Scope Duration}, "One Time", {2024 Admin Solutions Out of Scope Created Date}, >="01/01/24")

Thank you for your help in advance!

KPH

Hi

There are two problems

1) The format of the Date you are looking for.

"01/01/24" is a text string. So you can't do mathematical comparisons (like less than) on it. If you enter the date within a DATE function like this

DATE(24,01,01)

smartsheet will know this is a date

The order is year, month, day, so Jan 31 is DATE(24,1,31)

Then you can use it in your logical expression.

2) The system generated Created Date column is not a date type column so you can't do date comparisons directly

You can however use the DATEONLY function to extract the date part and then use that. You would need to do this in the sheet that holds the date. You can create another column and use

=DATEONLY(Created@row)

You can then make this a column formula and hide the column.

In your SUMIFS refer to the new column instead of {2024 Admin Solutions Out of Scope Created Date}

Your final formula would look something like:

=SUMIFS({2024 Admin Solutions Out of Scope Revenue Range 2}, {2024 Admin Solutions Out of Scope Duration}, "One Time", {2024 Admin Solutions Out of Scope Just Date}, >=DATE(24,01,01))

KPH

Hi

There are two problems

1) The format of the Date you are looking for.

"01/01/24" is a text string. So you can't do mathematical comparisons (like less than) on it. If you enter the date within a DATE function like this

DATE(24,01,01)

smartsheet will know this is a date

The order is year, month, day, so Jan 31 is DATE(24,1,31)

Then you can use it in your logical expression.

2) The system generated Created Date column is not a date type column so you can't do date comparisons directly

You can however use the DATEONLY function to extract the date part and then use that. You would need to do this in the sheet that holds the date. You can create another column and use

=DATEONLY(Created@row)

You can then make this a column formula and hide the column.

In your SUMIFS refer to the new column instead of {2024 Admin Solutions Out of Scope Created Date}

Your final formula would look something like:

=SUMIFS({2024 Admin Solutions Out of Scope Revenue Range 2}, {2024 Admin Solutions Out of Scope Duration}, "One Time", {2024 Admin Solutions Out of Scope Just Date}, >=DATE(24,01,01))

Christina C

@KPH - Thank you for your assistance. I suspected that I could not use the system created date column as I intended. I have used the =DATEONLY before and will use that. Thank you for the clarification on order of month etc.

Really appreciate your help!! Thanks.

KPH

No problem, glad I could help.