Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding the Sum of value each month
I have an excel spreadsheet which I use each month to record work done, each entry has a date and a value in £'s associated with it. What i'd like to be able to do is have a seperate field for each month which would just provide a sum of the amounts to date in that month taken from the individual entries made. The issues i have are i) There are a different number of individual entries each month, one month it may be 5 the next 15 ii) I'm reasonably inexperienced with anything other than the basic excel and I don't know where to start with this -- Pjcan1 ------------------------------------------------------------------------ Pjcan1's Profile: http://www.excelforum.com/member.php...o&userid=24277 View this thread: http://www.excelforum.com/showthread...hreadid=378924 |
#2
|
|||
|
|||
Hi there, I know how frustrating it can be when your so close to a solution, but you just can't quite make it. I have a solution below which i belive is fairly straight forward for you to implement. If you have any problems/questions then please let me know. ----------Solution---------- In a new separate column add the following formula =MONTH(XX)[/b] WHERE XX IS THE CELL THAT HAS THE DATE IN FOR 1 ROW. ONCE YOU HAVE DOWN THIS COPY THIS FORMULA DOWN THE SHEET SO THAT FOR EVERY ROW THAT HAS A DATE IN YOU ALSO HAVE THIS FORMULA. THIS FORMULA IS TELLING YOU THE NUMBER OF THE MONTH THAT CORRESPONDS TO THE ENTERED DATE (SO JAN = 1 AND DECEMBER =12) NEXT IN THE CELL WHERE YOU WANT TO CREATE THE SUM, ADD THE FOLLOWING FORUMULA *=SUMIF(AA,B,CC) whe aa is the new column that we created above b is the month you wish to sum (so if you want to see teh sum for march enter 3) cc is the column that has the value that you wish to sum If you do this i belive that you will solve your problem. If you have any further problem please let me know, and i can post a demo sheet with the solution explained above Best regards, Rob Turnbull [b]Sortoutmyexcel.com* -- Rob Turnbull ------------------------------------------------------------------------ Rob Turnbull's Profile: http://www.excelforum.com/member.php...o&userid=24278 View this thread: http://www.excelforum.com/showthread...hreadid=378924 |
#3
|
|||
|
|||
You could also have a look at Pivottables: http://www.datapigtechnologies.com/ExcelMain.htm Ola Sandström -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=378924 |
#4
|
|||
|
|||
Assuming that the dates are in A2:A100, amounts in B2:B100, you can use
=SUMPRODUCT(--(TEXT(A2:A100,"mmm")="Jan"),B2:B100) etc. -- HTH Bob Phillips "Pjcan1" wrote in message ... I have an excel spreadsheet which I use each month to record work done, each entry has a date and a value in £'s associated with it. What i'd like to be able to do is have a seperate field for each month which would just provide a sum of the amounts to date in that month taken from the individual entries made. The issues i have are i) There are a different number of individual entries each month, one month it may be 5 the next 15 ii) I'm reasonably inexperienced with anything other than the basic excel and I don't know where to start with this -- Pjcan1 ------------------------------------------------------------------------ Pjcan1's Profile: http://www.excelforum.com/member.php...o&userid=24277 View this thread: http://www.excelforum.com/showthread...hreadid=378924 |
#5
|
|||
|
|||
That didn't work, I just ended up with #Name? in the cell. Also the problem I have is that as with the dates the value apportioned for each entry follow one another so I don't want to sum the column, it looks something like this Date Value 02/04/05 10000 12/05/05 12500 15/05/05 2500 21/05/05 10000 30/05/05 12500 02/06/05 12500 All I want to do is work out how many entries there are for each month and then total up the associated values for that month, so in another part of the workbook I want something like Total Apr xxxx Total May xxxx Total Jun xxxx Does this make sense? -- Pjcan1 ------------------------------------------------------------------------ Pjcan1's Profile: http://www.excelforum.com/member.php...o&userid=24277 View this thread: http://www.excelforum.com/showthread...hreadid=378924 |
#6
|
|||
|
|||
Which didn't?
-- HTH Bob Phillips "Pjcan1" wrote in message ... That didn't work, I just ended up with #Name? in the cell. Also the problem I have is that as with the dates the value apportioned for each entry follow one another so I don't want to sum the column, it looks something like this Date Value 02/04/05 10000 12/05/05 12500 15/05/05 2500 21/05/05 10000 30/05/05 12500 02/06/05 12500 All I want to do is work out how many entries there are for each month and then total up the associated values for that month, so in another part of the workbook I want something like Total Apr xxxx Total May xxxx Total Jun xxxx Does this make sense? -- Pjcan1 ------------------------------------------------------------------------ Pjcan1's Profile: http://www.excelforum.com/member.php...o&userid=24277 View this thread: http://www.excelforum.com/showthread...hreadid=378924 |
#7
|
|||
|
|||
Perfect sense. Use this: Count: =SUMPRODUCT(--(TEXT($A$10:$A$15,"MMM")=A3)) Sum: =SUMPRODUCT((TEXT($A$10:$A$15,"MMM")=A3)*($B$10:$B $15)) See encl. zip-file: http://www.excelforum.com/attachment...tid=3505&stc=1 Hope it worked Ola Sandström Note: You have to change Maj (swedish) to May (english) +-------------------------------------------------------------------+ |Filename: Book4.zip | |Download: http://www.excelforum.com/attachment.php?postid=3505 | +-------------------------------------------------------------------+ -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=378924 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding min and max date of a given month | Excel Worksheet Functions | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions | |||
Accounting Month vs. Calendar Month | Excel Worksheet Functions | |||
How to extract month number from month name | Excel Discussion (Misc queries) |