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.

Formula to show monthly count

02/15/16 Edited 12/09/19

Hi, I am trying to figure out a formula that tells me how many entries have been entered for a particular month. I have a date column and an tonnage column so if I have 10tonne of material made on 1st February i would enter in the date column 01/02/16 and next to it in the tonnage column I would enter 10. I also have a total tonnage for February column, total tonnage for March column and so on for the whole year. I am trying to figure out a way to get the amount of tonnage entered for february listed in my total tonnage for february column as the amount of entries. For example if I had 6 entries under the date column listed as february I want the total tonnage for February to say 6. Any ideas on formulas for this one at all?

Many thanks, Irene

Comments

  • JamesRJamesR ✭✭✭✭

    There are two ways to tackle tis:

    1  Create a Column that extrapulates the month from the date - = Month(Date).  This will give you a number between 1 and 12.

    In the Feb column usr a Sumif to add all the values fron the tonnage column if the month colum =2

     

    2 Redesign the sheet ito Hierarchys by month and use the =Sun(Children() to give you the totals for each month.

  • edited 02/16/16

    Thanks for your help James, your first option is the one I was thinking of using but don't know how to write the formulas to get the month to be number two if the date is 01/02/16 for example, any ideas?

    Many thanks.

  • Sorry James, just found the answer! Thank you for your help I've set it all up now :)

  • SORRY JAMES!! The formula I did for 'In the Feb column usr a Sumif to add all the values fron the tonnage column if the month colum =2' hasn't worked out......any suggestions?

     

  • JamesRJamesR ✭✭✭✭
    • Syntax: SUMIF(criteria_range, criteria_value, sum_range)

    Criteria_range = Column that contains the Month Number (Month:Month)

    Criteria_value = 2

    sum_range = column that has the numbers to add up(Tonnage:Tonnage)

    =SUMIF(Month:Month,2,Tonnage:Tonnage)

     

     

     

This discussion has been closed.