Sum for last calendar month & year

[seaquest]

I have a spreadsheet with column H = date and column J is a numerical
value. I am looking for a way to sum all entries in the last calendar
month and also for the last calendar year (Jan1 -Dec 31). I was hoping that Excel could determine the last month based on the current date TODAY(). It would look at the current month and Sum the J
field for the previous month.

The last calendar year function would look at all entries in the
previous year Jan 1 thru Dec 31. If the current date is in 2006 it
would look at all entries dated 2005.

 

[venkat1926]

I presume you meant by "last" month as "previous" month to today.
=month(today())-1 will give today 8 i.e. august.
similarly you can try
=year(today())-1 will give you 2005


then use sumif if there is only one condition otherwise sumproduct.
see help for these functions in excel help

venkat

 

[seaquest]

Ok thanks. I'm trying that using this formula

=SUMIF(H:H,">"&DATE(month(today())-1,J:J))

but it's returning a 0 value. Am I using it correctly?

Jim

 

[venkat1926]

i prefer sumproduct
see sample sheet below
fomulas in H14 and H15 and the comment in J 14

in suproduct do not use columns like (H:H") but the range H1:h12. arrays shuould of same size. rows and columns should not be meixed

a good url for tis is

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Book1
	H	I	J	K	L
1	11-Sep-06		1
2	18-Sep-06		2
3	14-Aug-06		3
4	22-Sep-06		4
5	4-Sep-06		5
6	4-Sep-06		6
7	9-Sep-06		7
8	22-Sep-06		8
9	17-Aug-06		9
10	15-Aug-06		8
11	22-Sep-06		7
12	20-Sep-06		6
13
14	20		invokebycontrolshiftenter
15	20
Sheet1

 

[venkat1926]

if you prefere sum(if....) not sumif here is the formula for the sample sheet sent earlier

=SUM(IF(MONTH(H1:H12)=MONTH(TODAY())-1,J1:J12))
mpte ot os sum open bracket if open bracket etc

INVOKE BY CONTROL SHIFT ENTER

correction to my previous message

previous sumproudct need NOT necessrily be invoked by control shift enter .
that can be invoked by just ENTER.

 

[Fergus]

Or, if you want to link it to the TODAY() function so that it automatically changes each month then try this longer formula:

=SUMPRODUCT(--(MONTH(H2:H1000)=MONTH(TODAY())-1),--(YEAR(H2:H1000)=YEAR(EOMONTH(TODAY(),-1))),(J2:J1000))

This also caters for when today is a date in January but you want last year December totals. For the EOMONTH function you need to activate the Analysis Toolpack Add-in

 

[seaquest]

Thanks everyone for all the tips, unfortunately they all end up with an error "VALUE Range has no entry corresponding to this cell". I'm wondering if this is because the date field is an actual date in the format dd-mmm-yyyy rather than just month alone?

 

[Fergus]

I'm wondering if this is because the date field is an actual date in the format dd-mmm-yyyy rather than just month alone?

No, that's not it - those formulae will only work if column H contains real dates. But I don't understand your error message as the formulae will work for the circumstances you described. Do you mean it shows #VALUE or are you using vba and getting the error message you quoted? Can you post a sample of your worksheet showing columns H to J over the first few rows using Colo's HTML Maker at : http://www.puremis.net/excel/downloads.shtml also refer to : http://www.mrexcel.com/board2/viewtopic.php?t=92622 Show us what formula you are using, tell us where you are putting it and what result it gives.