Getting the sum of column based on equal dates

msvb6109

New Member
Joined
May 20, 2012
Messages
5
Hello everybody. Monthly, I am making a report wherein I need to get the sum of a column based on specific dates. Attached is extracted sample of the data:
A B C
1 Value Date Amount Sub Total
2 01/04/2012 89,812.10
3 02/04/2012 147,543.00
4 02/04/2012 28,660.00
5 03/04/2012 79,950.92
6 03/04/2012 4,550.00
7 03/04/2012 145,770.00

What I am doing is to use the Sum function on the next column based on the date I need to add. For example, formula for c2 will be =sum(b2:b2), c3 will be blank since I needed to add b3 and b4, i.e., formula for c4 will be =sum(b3:b4), formula for c7 will be = sum(b5:b7).

Is there any way to expedite this process by wrting only one formula in column c that will be applicable to all? Thank you.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Maybe like this - formula in C2 copied down

Excel Workbook
ABC
1DateValueTotal
201/04/201289,812.10
302/04/2012147,543.00176,203.00
402/04/201228,660.00176,203.00
503/04/201279,950.92230,270.92
603/04/20124,550.00230,270.92
703/04/2012145,770.00230,270.92
Sheet6
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Or maybe better

=IF(A2<>A3,SUMIF(A$2:A$10,A2,B$2:B$10),"")
 

msvb6109

New Member
Joined
May 20, 2012
Messages
5
Mr./Ms. VoG,

Thank you very much for your prompt reply. This partly solves my problem. However, I need the sub total to appear in column c once only for every date. Thus, for columns c3, c5 and c6, these should be = to zero/nil. Can you help me on this? Thank you again.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Yep - see the second formula that I posted

Excel Workbook
ABC
1DateValueTotal
201/04/201289,812.10
302/04/2012147,543.00
402/04/201228,660.00176,203.00
503/04/201279,950.92
603/04/20124,550.00
703/04/2012145,770.00230,270.92
Sheet6
 

msvb6109

New Member
Joined
May 20, 2012
Messages
5
That is exactly what I needed. This will save me a lot of time. Thank you very much.
 
Last edited:

Forum statistics

Threads
1,081,556
Messages
5,359,552
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top