Cumulative Sum of Units Per Week

[chullan88]

Hi,

I am trying to make a summary for a units distribution on a weekly basis.
I have the units distribution data on a daily basis which needs to be summarised per week.( The cut-off day is Monday)
First Monday in the data is 21-Nov-16 so all units upto and including 21 should be added up in the cumulative units.
Similarly near to date 28-Nov units for dates 22,23,....upto 28 should be added up.
I require a formula to get this result in Column C (I have attached workbook with data and desired output)

VBA Request
----------------
I would like to get a VBA solution for the same.
After the cumulative units for all Mondays are obtained,the other rows should be deleted and only the Monday dates with cumulative values should remain.
Also the column for daily units should be deleted.


Thanks in advanc

[Jonmo1]

Try this in C2 and filled down
=SUM(Data!$B$3:INDEX(Data!$B:$B,MATCH($A3,Data!$A:$A,0)))-SUM($C$2:$C2)

[DannyJ]

The sit is throwing me all over the place at the moment. I don't know if this attachment will actually attach. Basically all I did was write a formula that says If the date is a monday sum the cell to the left adn the 6 cells above. If not then "Delete"

[chullan88]

Hi Jonmo1,

thanks for the reply
but it doesnt work

[Jonmo1]

Works for me.

[DannyJ]

Chullan,

Is you look at my workbook that I attached and use the VBA code:

Please Login or Register  to view this content.

That should do what you want.

[chullan88]

Works for me.

This formula works...

Thanks...

But actually i wanted the formula to be in the same sheet itself..

The desired output sheet is with cells deleted afterwards..

[chullan88]

Chullan,

Is you look at my workbook that I attached and use the VBA code:

Please Login or Register  to view this content.

That should do what you want.

hi dannyj,

it deletes the other rows but the cumulative value gets wrong after running the macro

[chullan88]

Chullan,

Is you look at my workbook that I attached and use the VBA code:

Please Login or Register  to view this content.

That should do what you want.

hi dannyj,

it deletes the other rows but the cumulative value gets wrong after running the macro

[Jonmo1]

I see.

Try
=SUM($B$3:INDEX($B:$B,MATCH(WORKDAY.INTL($A3-1+(7*(ROWS($A$1:$A1)-1)),1,"0111111"),$A:$A)))-SUM($C$2:$C2)

[chullan88]

I see.

Try
=SUM($B$3:INDEX($B:$B,MATCH(WORKDAY.INTL($A3-1+(7*(ROWS($A$1:$A1)-1)),1,"0111111"),$A:$A)))-SUM($C$2:$C2)

It gives value corresponding to Mondays but I need the values to appear only in the cell next to Monday

i.e in Sheet Data,cell C3 and C4 should be blank and then C5 should have value ...

[chullan88]

I see.

Try
=SUM($B$3:INDEX($B:$B,MATCH(WORKDAY.INTL($A3-1+(7*(ROWS($A$1:$A1)-1)),1,"0111111"),$A:$A)))-SUM($C$2:$C2)

It gives value corresponding to Mondays but I need the values to appear only in the cell next to Monday

i.e in Sheet Data,cell C3 and C4 should be blank and then C5 should have value ...

[Jonmo1]

Oh, well that makes it even easier.

Try this in C3 and filled down.
=IF(OR(WEEKDAY(A3)=2,A4=""),SUM(B$3:B3)-SUM(C$2:C2),"")

[chullan88]

Oh, well that makes it even easier.

Try this in C3 and filled down.
=IF(OR(WEEKDAY(A3)=2,A4=""),SUM(B$3:B3)-SUM(C$2:C2),"")

Excellent!!

Thank you so much..

My problem is partially solved , could you help with the macro?

[Jonmo1]

You're welcome.

[DannyJ]

Sorry, didn't think of the formulae.

Try this, it pastes the values of the formulae before deleting.

Please Login or Register  to view this content.

Use Jonmo's formula, I changed this to work with that one.

[chullan88]

Hi DannyJ,

Maybe because Im not so familiar with VBA, I can't get it to work.
Can you send me a workbook with the above macro?

Thanks

[peterrc]

Hi,

Or you could do it with a Pivot Table - please see attached.

Regards

peterrc

[chullan88]

Hi peterrc,

Thanks for the new take on the problem.

[JohnTopley]

In B3 of "Desired output"

=SUMIFS(Data!B:B,Data!A:A,">=" &A3-6,Data!A:A,"<=" &A3)

copy down

[chullan88]

Thanks for your message JohnTopley.

Could you please look into the VBA part?


Regards

[DannyJ]

Chullan,

Change with Sheet 1 to whatever sheet number it is. (The sheet number is the numbers on the right of the brackets in vba)

It will be displayed as Sheet 1 (Name of sheet).

The vba code works fine for me, what error are you getting?

[JohnTopley]

VBA creates totals then deletes rows (using amended VBA from DannyJ)

Input:" Data" Output: "Output"

Please Login or Register  to view this content.

[chullan88]

VBA creates totals then deletes rows (using amended VBA from DannyJ)

Input:" Data" Output: "Output"

Please Login or Register  to view this content.

Thanks dannyj and JohnTopley!!
It works!!