Welcome!

Sum up data NOT using parent rows

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

Find more posts tagged with

Comments

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