Hi I have a query that I hope someone can help me with.



I record daily sales by shop as follows and all data needed is in Row 11.

Monday Tuesday
shop1/ shop 2/ shop 3 /shop4 /shop 5 shop1/ shop 2/shop 3/shop4 /shop 5
A11 B11 C11 D11 E11

On a separate worksheet I want to sum the total sales by day and record them as seen below in my summary sheet;

Monday Tuesday Wednesday
Week 1
Week 2
Week 3

However when I try and copy across from A11:E11 by using =sum and then copying across to the next cell in my summary sheet
, all I get is B11:F11 When I really want E12:E16

I would really appreciate if someone could solve this as I've spent hours to no avail!
Thanks in advance

Attached Files

Sample data.xlsx (11.0 KB, 11 views)

Could you please post a sample sheet ? ( Click Go advanced - Manage attachments).Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I am not sure if I have understood you correct.

Attached Files

Sample data_2.xlsx (12.1 KB, 12 views)

That worked a treat - brilliant expertise & have a great day!

Here is one solution for your problem make the data in more of a chart setup see my revised sample

Attached Files

sample data solution.xlsx (11.1 KB, 14 views)

Hi I have used the formula kindly provided by Xor however I cannot get it to apply as I begin a new week.
Please see attached and you will see when I copy down to week 2 the formula does not calculate correctly. Can you please advise?

Thank you in advance

Attached Files

Copy of Sample data_3.xlsx (12.5 KB, 12 views)

Where will data for week 2, 3, etc. appear. In Data Sheet row(13), row(14) etc.?

Please try to fill in manually some of the wanted results.

Hi, the data for week 2 starts from Monday Jan 8th found in the data sheet cells AJ13-AN13.The amount of money that should show for Jan 8th (Cell B4 on the summary sheet) is £130 but the formula does not copy down. I have filled in all of week 2 data on the data sheet so hopefully this is usable but if you need more, please let me know

1. Can you accept that data (as in Data Sheet) are arranged another way than shown (vertically instead of horizontally).

2. What kind of dates (in Data Sheet row 11) are we talking about. I wonder which year was 1 January a Monday. Can we use real Excel dates instead of strings like "Monday January 1st.

3. For how many weeks will there be data.

4. Will it be necessary to distinguish between shops (It doesn't seem so in your Summary Sheet).

5. Can you accept a pivot table or must it be a formula solution?

In the attached I have created a named range, d. See Name Manager. And I have inserted a new sheet, Data2 to make the calculations in Summary Sheet easier.

Attached Files

Copy of Sample data_4.xlsx (20.4 KB, 9 views)

Hi Xor I prepared answers to your first questions (pls see attached) before I saw your latest post about named ranges- based on the more up to date information in Sample 5 would the named range also work for the whole year?

Attached Files

Sample Data 5.xlsx (67.9 KB, 11 views)

Not sure if this is what you want.

Attached Files

SampleData_6.xlsx (94.0 KB, 11 views)

Hi Xor this looks very promising - I'm not familiar with name manager and how it works so cannot see where the 2 names have been created but the main thing is the results show on the weekly summary. Is there a way that the data that you have put in the data sheet under cells F20-K385 can be moved to a separate sheet - as there is already data that will go in these cells? If so please provide example many thanks

What is it that you are trying to do in Data Sheet F11:K11 and Data Sheet F14:K16?

All those #REF! are slowing down Excel considerably!

Hi, the data sheet does not slow my machine at all but the F14 - K16 sums all of the product sales by shop and the F11 -K11 provides a % breakdown of the product sales. Is there a way to just leave the data sheet as is (as this causes no problems) and extract the daily sales data by shop into a separate new sheet so that I can see a summary of the weekly sales? Thanks