Sum for last calendar month & year

[jsollows]

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

Can anyone help me with this?


Jim

 

[Guest]

Try something like this:

With
Dates in A1:A40
Values in B1:B40

The total for the month prior to today's month:
C1: =SUMPRODUCT(--(TEXT(A1:A40,"yyyymm")=TEXT(TODAY()-DAY(TODAY()),"yyyymm")))

The total for the year prior to today's year:
C2: =SUMPRODUCT(--(YEAR(A1:A40)=(YEAR(TODAY())-1)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[jsollows]

Thanks for the tip but I don't think it's quite what I need. The
spreadsheet column H containes dates for all entries. I want to produce
a sum of column J for all entries dated in the last calendar month
without the need to specify the month.

Jim

 

[Guest]

A couple questions....
If you don't know what month you're in, how will you know what last month is?
Are the dates for just one month?
year to date through last month?
All history through yesterday?

Can you describe the data in a bit more detail?
Are there any constraints we should be aware of?

***********
Regards,
Ron

XL2002, WinXP

 

[jsollows]

Hi Ron

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 spreadsheet holds several years of data and all dates are in a
column formatted as Date dd-mmm-yyyy

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.

Does that explain it any better?

Jim

 

[Guest]

Um...out of curiosity....
Did you try the formulas I posted?
(They do just what you described.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[jsollows]

Hi Ron

I must be missing something then because all I get when I use either
formula is a Parse Error.

Jim

 

[Guest]

Parse error? Not sure where that would be coming from. You should be able to
copy the below formulas into your workbook. (I changed the column refs for
dates and values to H and J, respectively.

The total for the month prior to today's month:
=SUMPRODUCT(--(TEXT(H1:H40,"yyyymm")=TEXT(TODAY()-DAY(TODAY()),"yyyymm")))

The total for the year prior to today's year:
=SUMPRODUCT(--(YEAR(H1:H40)=(YEAR(TODAY())-1)))

Note: in case text wrap impacts the display, there are NO spaces or line
breaks in those formulas.

Does that help?

If No....please post the formula you are using. We'll see if we can spot
the issue.

***********
Regards,
Ron

XL2002, WinXP