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.

Sum up data NOT using parent rows

Kylie D'Costa
Kylie D'Costa ✭✭
edited 12/09/19 in Archived 2017 Posts

Hi team,


got a tricky one me thinks..... (screen show below for assistance).


I need to add the quantity required on any given day.

I was using parent rows - which worked fine, until the dates went out of sequence - which will happen and must happen given our workflows.


Basically I want to know at any point in time the total sum of quanitity for each date required.


So, using the example below:

98+150+160 = total of 264 for 2/2/17

150+150 = total of 300 for 25/01/17



I think its tough because the date required is not sequential.....


Any ideas?





  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    edited 02/03/17

    If a new column, SpecificDate, has the values ofthe dates you want to add up, the SUMIF formula below will be your new best friend.


    =(SUMIF([Date Required]:[Date Required], SpecificDate1, [Quantity]:[Quantity]))

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Kylie, Brett,


    Unless the ranges  also include children rows.


    If so, you might need to yet another column to determine if you are in a child or parent row, and then change your SUMIF to a SUMIFS (multiple criteria)


    If you have grand-children, it gets a little more complicated, but is still possiible using formulas. (sometimes, it is easier to check 10 boxes than to create a formula to check 10 boxes out of 100)



  • Hi Brett,


    great suggestion, however I an not looking for a particular date. I need to know the total volume for each date that has been selected.



    So, using the example from the screen shot above:

    98+150+160 = total of 264 for 2/2/17

    150+150 = total of 300 for 25/01/17


    I dont know how I would make the SUMIF formulas work? 


    Sorry to be a pain guys! This one is killing me.



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    98+150+160 = 408.

    But no matter.


    Where is your formula going to reside?

    Do you have a header section?


    Let's suppose that you do and it consists of two rows.

    Using Brett's formula (slightly modified for your column names)


    Add a column to put this equation in.


    =(SUMIF([Date Required]:[Date Required], [Date Required]1, [Quantity (m3)]:[Quantity (m3)]))


    In the [Date Required] row 1 cell, put the 2/2/17 date.


    This formula looks at the row's [Date Required] column and looks for matches in the rest of the [Date Required] column. When it does, it add any value in [Quantity (m3)] to its sum.

    Since row 1 does not have any value in the [Quantity (m3)] column, it won't affect the total.


    In row 2, 

    =(SUMIF([Date Required]:[Date Required], [Date Required]2, [Quantity (m3)]:[Quantity (m3)]))


    with a date of Jan 25th and you should see the pattern start to emerge.


    Hope this helps.







This discussion has been closed.