Sum for last calendar month & year

seaquest

New Member
Joined
Sep 17, 2006
Messages
3
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

New Member
Joined
Sep 17, 2006
Messages
3
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
HIJKL
111-Sep-061
218-Sep-062
314-Aug-063
422-Sep-064
54-Sep-065
64-Sep-066
79-Sep-067
822-Sep-068
917-Aug-069
1015-Aug-068
1122-Sep-067
1220-Sep-066
13
1420invokebycontrolshiftenter
1520
Sheet1
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
Or, if you want to link it to the TODAY() function so that it automatically changes each month then try this longer formula:
Code:
=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

New Member
Joined
Sep 17, 2006
Messages
3
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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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.
 

Forum statistics

Threads
1,141,680
Messages
5,707,789
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top