Sum values in a column based on date

Question

I have written this function that will give me a monthly sum for two columns  one has the date of each order  one has the cost of each order    SUMIF  C 1  C 1000   gt    amp DATE 2010 6 1   D 1  D 1000 -SUMIF  C 1  C 1000   gt    amp DATE 2010 7 1   D 1  D 1000    Using data like this   8 16 10 17 00 7 99 8 16 10 14 25 7 99  8 15 10 22 42 7 99   I end up with a table like this   May     998 June    968 28 July   1239 76 August  514 96   However  now I would like to do daily sums and using my way I have to hand edit each row     How can I do this better in Excel

User · Answer

Following up on Niketya s answer  there s a good explanation of Pivot Tables here  http   peltiertech com WordPress grouping-by-date-in-a-pivot-table   For Excel 2007 you d create the Pivot Table  make your Date column a Row Label  your Amount column a value   You d then right click on one of the row labels  ie a date   right click and select Group   You d then get the option to group by day  month  etc   Personally that s the way I d go   If you prefer formulae  Smandoli s answer would get you most of the way there   To be able to use Sumif by day  you d add a column with a formula like    DATE YEAR C1   MONTH C1   DAY C1     where column C contains your datetimes   You can then use this in your sumif

User · Answer

If the second row has the same pattern as the first row  you just need edit first row manually  then you position your mouse pointer to the bottom-right corner  in the mean time  press ctrl key to drag the cell down  the pattern should be copied automatically

User · Answer

Add a column to your existing data to get rid of the hour minute second time stamp on each row      DATE YEAR A1   MONTH A1   DAY A1     Extend this down the length of your data   Even easier  quit collecting the hh mm ss data if you don t need it   Assuming your date time was in column A  and your value was in column B  you d put the above formula in column C  and auto-extend it for all your data   Now  in another column  let s say E   create a series of dates corresponding to each day of the specific month you re interested in   Just type the first date   for example  10 7 2016 in E1   and auto-extend   Then  in the cell next to the first date  F1  enter    SUMIF C C  E1  B B     autoextend the formula to cover every date in the month  and you re done   Begin at 1 1 2016  and auto-extend for the whole year if you like

User · Answer

Use pivot tables  it will definitely save you time   If you are using excel 2007  use tables  structured references  to keep your table dynamic   However if you insist on using functions  go with Smandoli s suggestion   Again  if you are on 2007  use SUMIFS  it s faster compared to SUMIF

User · Answer

Use a column to let each date be shown as month number  another column for day number         A      B       C         D    -----  ----- ----------- -------- 1     8      6    8 6 2010   12 70 2     8      7    8 7 2010   10 50 3     8      7    8 7 2010    7 10 4     8      9    8 9 2010   10 50 5     8     10   8 10 2010   15 00   The formula for A1 is  Month C1   The formula for B1 is  Day C1   For Month sums  put the month number next to each month         E      F         G         -----  ----- -------------   1     7    July    1 000 010  2     8     Aug    1 200 300    The formula for G1 is  SumIf  A 1  A 100  E1   D 1  D 100    This is a portable formula  just copy it down      Total for the day will be be a bit more complicated  but you can probably see how to do it

[excel] Sum values in a column based on date

Examples related to excel