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.

Month Count/Summary as Integer

Options
Chris Jackson
edited 12/09/19 in Archived 2016 Posts

I have looked for examples but can not find - apologies for basic formula question.

From a date field i would like a formula that will take the month and total/count by month:

Eg:  Month 1 - Total 15

        Month 2 - Total 9

    to Month 12 - Total 0

 

Can anyone help?

many thanks

Chris

Comments

  • Chris Jackson
    edited 12/08/16
    Options

     

    Dont worry - got it.

    Extract Month into new column from Date for each row

    =MONTH([Actual Completed Date]1)

     

    Then total up each month (one entry for each month)

    ="Total DEC  -  " + COUNTIF(Month:Month, =12)

    Not sure if having 12 formulas is the most efficient - but it works

     

    Which can published on Site as a report 

     

     

     

    site_totals.jpg

  • Chris Jackson
    Options

    AHH - LOST IT AGAIN :)

    |If there is not date entered in the date field then =MONTH([Actual Completed Date]1) fails (no month to extract) - which then causes the COUNIF to fail.

     

    What tweak needs to be addded to ensure the =MONTH formula still works even if the Date column has no entry on that row?

     

    many thanks

     

    Chris

     

  • Angus Bruce
    edited 12/08/16
    Options

    if(isblank([Actual Completed Date]1),0,month([Actual Completed Date]1))

     

    or one of the other logic statements ; iserror, isdate ?

  • Hm.....there really is no formula to count the # of something for a current month, or last month, using the date column? Seems weird to have create another column that pulls out the month from a date row.

This discussion has been closed.