*Help* - Formula for Date / $ Columns
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!!!
Answers
-
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
-
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?
-
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.
-
Thank you so much!
Trying now! :)
-
Okay, so I input the formula your provided and this is the error message I received.
#INVALID DATA TYPE
This is so frustrating. Lol
-
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
-
OMG!!! IT WORKED!!!
I am going to send you FLOWERS!!!
Thank you, Kelly!!!!
-
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.
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!