*Help* - Formula for Date / $ Columns

Allison C

I am trying to get the dollar value from the "Proejct Amount" column base on the dates in the "Go Live Date" column.

Ex: I want to summarize the project value of all projects in 2022 and 2023 separately so that I can add the summaries in a dashboard.

The forumla I have tried is:

=COUNTIFS([Project Amount]:[Project Amount], =>=01/01/22", [Go Live Date]:[Go Live Date], =<=12/31/22", [Project Amount]:[Project Amount])

Any help is much appreciated! This is driving me BONKERS!!!

Kelly Moore

Hey @Allison C

The SUMIFS function will provide the dollar amount. It will sum the required column. The COUNTIFS formula provide how many occurrences you have.

=SUMIFS([Project Amount]:[Project Amount], [Project Amount]:[Project Amount],ISNUMBER(@cell), [Go Live Date]:[Go Live Date], AND(ISDATE(@cell), YEAR(@cell)=2022))

Will this work for you?

Kelly

Allison C

Thank you, Kelly!

I am going to try this for sure as it makes more sense than what I was doing! 😂

When you have the @cell - do I leave that or do I need to input the cell range in there?

Kelly Moore

Hey Allison

You will leave the @cell just as I have it. The @cell says to evaluate every cell in the range. Note when you use @cell that is must be written as lower case - just like @row must be lower case.

You should be able to drop the formula I provided just in your sheet - hopefully I counted the parentheses correctly. 😉

Let me know if you have any problems with this.

Kelly

PS. This reference provides info on @cell, if you are curious.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#:~:text=The%20%40cell%20argument%20performs%20a,making%20your%20formula%20more%20efficient.

Allison C

Thank you so much!

Trying now! :)

Allison C

Okay, so I input the formula your provided and this is the error message I received.

#INVALID DATA TYPE

This is so frustrating. Lol

Kelly Moore

Hey

Try this. I almost gave it to you like this originally

=SUMIFS([Project Amount]:[Project Amount], [Project Amount]:[Project Amount],ISNUMBER(@cell), [Go Live Date]:[Go Live Date], AND(ISDATE(@cell), IFERROR(YEAR(@cell),0)=2022))

I don't like to add IFERRORs until after I know that the formula actually works. If this doesn't clear it, as just a test, remove everything from the formula after the ISNUMBER(@cell). This will just test your Project Amount column to see if it is summable.

Kelly

Allison C

OMG!!! IT WORKED!!!

I am going to send you FLOWERS!!!

Thank you, Kelly!!!!

Kelly Moore

lol. I'm glad to help. This does indicate, though, that you have some data opportunities in your Date column that is causing some errors.

Allison C

Good afternoon!

You are probably a little worried that I am reaching out again so soon, I apologize. Your input was so helpful yesterday that I thought maybe you could help me with another issue. Again, I apologize for being a pest!

Today I am trying to get a formula to count how rows in the "deal type" column there are that contain "to SC+" in the year of 2022.

Here is a copy of the formula I have been trying most of the day, with no success. :(

=COUNTIFS([Deal Type]:[Deal Type], [Deal Type]:[Deal Type], CONTAINS("to SC+", @cell), [Go Live Date]:[Go Live Date], AND(ISDATE, @cell), IFERROR(YEAR, @cell), 0) = 2022)