Sales Totals By Year and Month

JORDTEE

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.

 

rjudenberg

use these for CRTSYEAR and CRTSMONTH

=IF(ISDATE(CRTS14), YEAR(CRTS14), 0)

=IF(ISDATE(CRTS14), MONTH(CRTS14), 0)

JORDTEE

Thank you rjudenberg! This will work just fine!

Robert S.

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)