display total sales at the end of each month from date-wise data

[Ranjeet2001]

Hello Everybody!
I had tried to be as descriptive as possible in the title. My excel problem is this --

Daily sales are recorded in column B against dates(dd/mm/yyyy) in column A.
Sometimes there are more than one record of sale for a day, and sometimes no sale at all for another day.
What I want is --

In the column C, a blank is displayed whilst it still is the same month for sales records except for the last record where it should display the sum of all sales of that month till that point of time(date); and when the month changes, the total for all sales for the last month be displayed in the cell in column C against the last date for sales in the last month, and again blanks to be displayed against the advancing date-wise sale records except for the last one where it should display the total of sales in this new month till that point of time(date).

For Example:--

Date **** Amount **** CumulativeMonthlyAmount
01-04-2014 **** 500
02-04-2014 **** 1000
02-04-2014 **** 1250
09-04-2014 **** 200
09-04-2014 **** 2000
09-04-2014 **** 500
09-04-2014 **** 800
29-04-2014 **** 3600
31-04-2014 **** 300 **** 12600
01-05-2014 **** 2450
08-05-2014 **** 1250
18-05-2014 **** 900
18-05-2014 **** 700
30-05-2014 **** 4500 **** 9800
03-06-2014 **** 1800
06-06-2014 **** 2100
06-06-2014 **** 300
06-06-2014 **** 1000 **** 5200

and if now I enter new Amount of 500 on 08-06-2014 in the next row, the 5200 in column C should disappear and 5700(=5200+500) should appear below it.


To clarify: Number of cells carrying the same date, and also of those belonging to the same month is variable for each month.
Can there be a nice and clean formula, instead of referencing and sub-referencing with data in hidden columns
How to use the SUMIF, or COUNTIF, or OFFSET, MATCH, "", SUMPRODUCT, etc to solve this problem?

Please help!

[mrice]

You can do this with a helper column.

Insert a column between B and C

In the (now empty) C2 paste in

=YEAR(A2)&LEFT("0",2-LEN(MONTH(A2)))& MONTH(A2)

and copy down. This will give a year and month concatenated value

In D2 paste in

=IF(C2<> OFFSET(C2,1,0),SUMIFS(B:B,C:C,C2),"")

and copy down. This does the sum for all rows with the same value in column C but only displays if the value in column C is different in the row below.

[Mazzaropi]

Ranjeet2001, Good afternoon.

Try to use it:

C2 -->
=IF(MONTH(A2)<>MONTH(A3),SUMPRODUCT((MONTH($A$2:$A$19)=MONTH(A2))*($B$2:$B$19)),"")
Copy it down.

Is it what you want?
I hope it helps.

Have a nice weekend.

[Ranjeet2001]

Hello mrice,
Thanks a lot for that. Though the formula you asked me to paste in C2 looked scary at first, I could figure out that it intended to keep month number in 2 digits!
But can I ask if we could have simply written C3 instead of "OFFSET(C2,1,0)? Anyway thanks again!

You can do this with a helper column.

Insert a column between B and C

In the (now empty) C2 paste in

=YEAR(A2)&LEFT("0",2-LEN(MONTH(A2)))& MONTH(A2)

and copy down. This will give a year and month concatenated value

In D2 paste in

=IF(C2<> OFFSET(C2,1,0),SUMIFS(B:B,C:C,C2),"")

and copy down. This does the sum for all rows with the same value in column C but only displays if the value in column C is different in the row below.

[Ranjeet2001]

Ranjeet2001, Good afternoon.

Try to use it:

C2 -->
=IF(MONTH(A2)<>MONTH(A3),SUMPRODUCT((MONTH($A$2:$A$19)=MONTH(A2))*($B$2:$B$19)),"")
Copy it down.

Is it what you want?
I hope it helps.

Have a nice weekend.

Hi Mazzaropi,
This one seems even better; basically i like to avoid helper columns as much as possible!
This was exactly what I wanted.
In fact I was almost there with this formula, but I had used A:A, and B:B instead of the absolute reference. I still wonder if we could have kept the second letter relative like $A$2:A100. I tried and it seemed to work fine. Please tell if I am wrong generally and only this particular case holds true. Just a minor point!
Thanks a ton anyway! Thanks