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

Options
JORDTEE
JORDTEE ✭✭
edited 12/14/17 in Archived 2017 Posts

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.

 

2017-12-14 11_48_03-TESTING COPY OF CURRENT PROJECT LOG - Smartsheet.com_.png

2017-12-14 11_48_03-TESTING COPY OF CURRENT PROJECT LOG - Smartsheet.com_.png

Comments

  • rjudenberg
    Options

    use these for CRTSYEAR and CRTSMONTH

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

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

  • JORDTEE
    JORDTEE ✭✭
    Options

    Thank you rjudenberg! This will work just fine!

  • Robert S.
    Robert S. Employee
    Options

    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)

This discussion has been closed.