Excel Sum if Based on Day (with duplicates)

 Solution -

My question is in regard to Microsoft Excel 2003. I track my finances and one of the issues I've run into is I have a table, in Column A there is the date and then in Column B I have the expense. Since there are multiple expenses for each day, I get duplicate dates. Is there anyway to get an average based on the day of the week with the information I have?

Essentially, I want to know that on average I spend x amount on Monday, Tuesday, etc.


1 reply

The easiest way for you to do this would be to Add Each day to its own section, making Monday's always A, Tuesdays always B, and so forth.

Then you go through (we will use 2 days ago till now as an example),
A2 Date of purchase 8/7/2009
A3 cost of purchase
A4 cost of purchase
A5 cost of purchase
A6 cost of purchase (final purchase of the day)
A7 Sum of A3 through A6 which would be written ignore the quotes "=SUM(A3:A6)
A8 Date of Purchase 8/8/2009
A9 Cost
A10 Cost
A11 cost
A12 cost
A13 cost
A14 cost
A15 cost
A16 cost
A17 Sum of A9 through A16 =SUM(A9:A16)
A18 Date 8/9/2009
A19 cost
A20 cost
A21 cost
A22 cost
A23 cost
A24 Sum of A19 through A23 =SUM(A19:A23)
A25 Monday Average =AVERAGE(A7,A17,A24)

This is the only way i can think to do it, you may even want to shift everything over 1 column, so you can put a labeling to the left of the sums so you know where they are, or just bold the text, but you will want to find some way to make them noticible. I know there is probably a far better way to do this, but I am not experienced enough with excel to know how.