#### 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

edited 12/09/19

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

Etc.

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

Any ideas?

Thanks

• ✭✭✭✭✭✭
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]))

• ✭✭✭✭✭✭

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)

Craig

• 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.

IE:

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.

Thanks,

• ✭✭✭✭✭✭

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.

Craig

This discussion has been closed.