Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Sales Totals By Year and Month
I'm trying to summarize our sales totals by year and then by month, based on a joining of the year and month from a "current ready to ship" date column. I'm using this for the January 2017 total cell:
=SUMIF(yearmonth14:yearmonth51, LEFT(@cell, 9) = "2017 - 1", [P.O. AMOUNT]14:[P.O. AMOUNT]51)
This method works just fine, but I need to have it return nothing to the yearmonth column when what is in the date column is "TBD" or blank. I get #INVALID DATA TYPE if "TBD" or blank. This makes my totals also invalid.
It seems that Smartsheet can't pull data from a date column when what is in the date column isn't a real date. I tried a few if(isblank ideas, and iferror ideas, but I can't seem to find something that will work. I also tried making a text/number column and use something like =[CURRENT READY TO SHIP]14 but it also comes out #INVALID DATA TYPE
Maybe there is a simpler way to report monthly totals from a sheet like this, but I need help to see it. In total, I'm trying to report this information to a "sights" dashboard as a metric.
Comments
-
use these for CRTSYEAR and CRTSMONTH
=IF(ISDATE(CRTS14), YEAR(CRTS14), 0)
=IF(ISDATE(CRTS14), MONTH(CRTS14), 0)
-
Thank you rjudenberg! This will work just fine!
-
Hello,
The formulas provided by rjudenberg will work for those columns to break out the year into a column and the month into a column. If you're looking for a way to total the sales totals by year and month with out using in other columns, you can do so with the SUMIFS(), YEAR(), MONTH() and @cell functions. You can find more on these functions and more on this help center article (https://help.smartsheet.com/functions). Here's what that formula would look like, including IFERROR() functions to allow for rows that don't have dates in the date column:
=SUMIFS([P.O. AMOUNT]:[P.O. AMOUNT], [CURRENT READY TO SHIP]:[CURRENT READY TO SHIP], IFERROR(YEAR(@cell), 0) = 2017, [CURRENT READY TO SHIP]:[CURRENT READY TO SHIP], IFERROR(MONTH(@cell), 0) = 1)
This formula is also referencing the entire columns, so if you only want them to reference a specified range like your formula does, that would look like this:
=SUMIFS([P.O. AMOUNT]14:[P.O. AMOUNT]51, [CURRENT READY TO SHIP]14:[CURRENT READY TO SHIP]51, IFERROR(YEAR(@cell), 0) = 2017, [CURRENT READY TO SHIP]14:[CURRENT READY TO SHIP]51, IFERROR(MONTH(@cell), 0) = 1)
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives