Adding Data According to Date

[ianmb]

Hi

I have a sales sheet and column A has got the date of the month. Column B has the total sales for the date in Column A.

The date is for every day for each month.

How can i add the total sales according to the corresponding date so that I have a total of the sales for a monthly basis?

Thanks in advance

Ian

[squiggler47]

=SUMIF(B2:B4,B7,C2:C4)

Where B2:b4 is the dates, c2:c4 is the values to sum and B7 is the date you wish to sum for!

or

=SUMIF(B2:B4,"01/01/2010",C2:C4)

[DonkeyOte]

The date is for every day for each month.

How can i add the total sales according to the corresponding date so that I have a total of the sales for a monthly basis?

If you're saying you wish to aggregate your data by month then I would suggest you use a Pivot Table and Group the date Field by Month & Year.

[ianmb]

Hi,

Thanks for your reply. I have tried it out but it is not wroking out. I amc not understanding what B7 is exactly.

I am attaching a sample of the workbook maybe it can help. Some dates are repeated as the sales may be done by different reps and i want to keep them seperate.

[DonkeyOte]

It's up to you how you do this but if you want to avoid Pivots* then to keep things simple and calcs lightweight I would suggest you do the following:

D2: =B2-DAY(B2)+1
copied down

change the values in I2:I5 such that they are real dates - ie enter 1/1/10 etc (apply custom format of mmm if you wish to alter appearance for aesthetic purposes)

with the above in place

J2: =SUMIF($D$2:$D$13,$I2,$C$2:$C$13)
copied down

there are plenty of alternatives to the above (two SUMIFs and/or SUMPRODUCT) but the above is pretty simple to implement.


*because you may want to list all months irrespective of whether or not they have sales