Count totals by week and month from separate worksheet

[cutcreative]

Morning everyone,

I got quite desperate by now, trying to figure out probably a very trivial thing and am stuck..
Creating a spreadsheet with sales, where the daily sales will add themselves in next workbook tab to weekly and same story in the next one: daily sales will add themselves to monthly.

=SUM command obviously doesnt work, since after creating this simple formula, copying it doesnt move to another 7 days as needed, but instead moves 1 column down and adds 7 next columns and so on. Neither the intelligent fill, with creating 3 columns and trying to fill rest automatically.

I am not very familiar with what tool to use, so I would appreciate any help.

All the days are in vertical manner, forms of payment in horizontal, therefore month of January cash takings are B2:B32, credit card C2:C32 and so on with 10 different fields as forms of payment.

2nd workbook tab is suppose to have weekly summaries and 3rd tab monthly.

I would very simply like to create a formula, that after copying will fill in the weeks or months respectively, simply by changing the data.
I dont need rounding up the numbers or anything alike.

All I need is: 1st sheet: Mon100, Tues150, Weds80, Thurs70, Fri200, Sat300...
...FORMULA..in the week Commencing 03.01 total 900
...copy/paste special in all the formula fields for week commencing 10.01,17.01... to do the same for me:

ADD NUMBERS FROM A RESPECTIVE NEXT 7 FIELDS, NOT JUMP DOWN BY ONE.


Would someone please help>?

Many thanks in advance!

[Azam Ali]

Hi cutcreative

Kindly upload a sample workbook

[cutcreative]

Hi Azam,

thanks for Your help.
Please see attached and let me know, whether You could help in any way.

Kind regards,

C

[brokenbiscuits]

You're much better off converting your dates to an actual date format, you can then manipulate the data much easier.

See attached for example.

[cutcreative]

Wow, thanks so much for your help!
That does the trick in a very easy way, I just will need to input half a year's of data and work on further part!

Absolute Godsend brokenbiscuit!

thanks ever so much! i'll be back soon!

Have a great week!

cheers

[royUK]

Is 1.01 the date? If it is you should be using a proper date entry.

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[cutcreative]

Sorry,

just clicked in, that it actually creates circular reference to the top cells as the first week doesnt calculate the top data correctly. (it takes week + 2days from previous week)

For some reason, when trying to adjust the formula to the 2nd row in weekly sales, circular reference is created and i have noticed, that top row ('Weekly Sales'!) calculates everything from the top, not just one week.
Also, Whenever the data is changed in 'Daily Sales' tab, the weekly one, although seeming to be formulated correctly doesnt change its value?

Would You please have a go?
Since last reply, Ive been seating on it, trying not to bother anyone, but the spreadsheets dont seem to like me!

Attached is the spread with a formula, that I cant really comprehend.. However I try to change it, in daily sales tab, the weekly doesnt update and whatever I do to formula it doesnt work either.. =(

Thank You for your time.

[brokenbiscuits]

I'm not 100% sure I know exactly what you mean, but does this work ok?

[cutcreative]

Ha!

It is now, I have double checked!
Great and thanks so much for all your help!

much appreciated!