Sum 2 years of data by day of the week

QuestionEdit

QUESTION: Hola Ricardo,

I have a spreadsheet which has 2 years of data and growing. It has dates in column A and sales in column B eg: A B 29/04/10 156 30/04/10 232

I want it to sum up the entire sheet by day of the week so total for Monday, total for Tuesday etc etc. My result would look like : Monday: 3456 TUesday: 4556 etc

I have worked out I can use a array and did this for mondays: {=SUM(IF(WEEKDAY($A$3:$A$574,2)={1},$B$3:$B$574))}

Tuesdays is: {=SUM(IF(WEEKDAY($A$3:$A$574,2)={2},$B$3:$B$574))}

etc etc. Problem is i can only use it in cells that have data. If i want it to be say ($A$3:$A$1000,2)={2},$B$3:$B$1000)) I get errors do you have any suggestions? I am very new to excel- well its been a year now and still trying....

Also

ANSWER: Yes, that,s the problem with using conditions in an array formula,

I assume the goal is not to update the formula each time you add another row

I suggest you to use dynamic names.

For example, instead to $A$3:$A$574 use "Date", and instead of $B$3:$B$574 use "Amount"

then you can set up those names to automatically grow or shrink with the amount of rows you are putting in

to do so you go to insert name define, name it Date, and the fill the refer to field with the following formula: =OFFSET(Sheet3!$A$3,0,0,COUNTA(Sheet3!$A:$A)-1,1) and the ok

do the same with the name amount and with this formula =OFFSET(Sheet3!$B$3,0,0,COUNTA(Sheet3!$A:$A)-1,1)

Its not a typo, I wanted both to grow with counting cells in column A because both ranges needs to be same sized

What that formula is doing is counting how many dates are in column A and the create a range starting from A3 and end depending on how many dates are.

You will have to keep a clean sheet for this, specially on column a and b, for example if you add a comment on column A the range will grow and might lead it to an error

then you change your formula to this

=SUM(IF(WEEKDAY(Date,2)=1,amount)) and so on...

check it out here http://www.4shared.com/file/wqUYrYsG/dynamic_name.html

---------- FOLLOW-UP ----------

QUESTION: for insert name define, to make it dynamic do i select the entire row (so when the data grows) Also when i insert name define can i use any name i want?

what does it mean =OFFSET and what is COUNTA?

Thank you soo much for your clear data, you cant beleive how long I have been struggling with this..

ANSWER: No matter what you select (entire row, entire column or even one cell) when going to the define name menu, it will change when you write and specific formula in the refer to field.

You can use whatever name you like as long as it doesn¡¯t start with a number and doesn¡¯t contain spaces.

COUNTA: Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or ar OFFSET: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Together they create a dynamic range depending on how many cells are not empty (COUNTA)

I'm glad to help

---------- FOLLOW-UP ----------

QUESTION: Wow ricardo The column D you created - I understand this will tellfriday = 1 , sat =2 etc. I can still use the define names, dates and amounts without the addition right? eg: instead of your : =SUM(IF(WEEKDAY(Date,2)=D3,amount))

I could point directly to amount: =SUM(IF(WEEKDAY(Date,2)= amount))

I am hoping thenwould auto-asign Sundays, to 1, Monday to 2 etc.?

Also what is the ,2 after date in the =SUM?

With regards to: =OFFSET(Sheet3!$A$3,0,0,COUNTA(Sheet3!$A:$A)-1,1)

-Where you have (Sheet3!$A$3,0,0 what are the 3,0,0,?

The last part -1 I understand it the entire row minus row 1 but what is the ,1 beside it?

Again a million thank you's I cannot tell you how much help this is

AnswerEdit

Hi

You still must tell the formula which day to sum, either how you did it {1} or referencing it D3

that ,2 after the date means how the formula will have it results, 2 means monday=1, tuesday=2 ...

if you instead use ,1 sunday=1 monday=2 and so on

In regards of OFFSET, that 3 is actually the reference to cell Sheet3!$A$3, and the two following zeros means how many columns and rows will move the offset

for example if I type in: Sheet3!$A$3,0,0 then the first cell of the created range will be Sheet3!$A$3 Sheet3!$A$3,1,0 then the first cell of the created range will be Sheet3!$A$4 Sheet3!$A$3,0,1 then the first cell of the created range will be Sheet3!$B$3 Sheet3!$A$3,1,1 then the first cell of the created range will be Sheet3!$B$4

and the last 1 means that your range is one column width

Congratulations for taking the time to understand the formula!, you will master it very soon