Finding the Sum of value each month

P

Pjcan1

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
 
R

Rob Turnbull

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)

where:

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
Sortoutmyexcel.com*
 
B

Bob Phillips

Assuming that the dates are in A2:A100, amounts in B2:B100, you can use

=SUMPRODUCT(--(TEXT(A2:A100,"mmm")="Jan"),B2:B100)

etc.
 
P

Pjcan1

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?
 
O

olasa

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.php?attachmentid=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 |
+-------------------------------------------------------------------+
 
Top