# Sum for last calendar month & year

#### seaquest

##### New Member
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

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

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

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.

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

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?

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.

Replies
0
Views
253
Replies
6
Views
342
L
Replies
8
Views
769
Legacy 352679
L
Replies
1
Views
209
Replies
11
Views
2K

1,220,980
Messages
6,157,187
Members
451,404
Latest member
Probe

### 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.

### Which adblocker are you using?

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

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