Sum up data NOT using parent rows

Kylie D'Costa

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

 

Kylie D'Costa

Brett Evans

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

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

Kylie D'Costa

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,

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.

 

Craig