| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum for last calendar month & year
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum for last calendar month & year
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 " wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum for last calendar month & year
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 Ron Coderre wrote: 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 " wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum for last calendar month & year
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 " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum for last calendar month & year
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 Ron Coderre wrote: 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 " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum for last calendar month & year
Um...out of curiosity....
Did you try the formulas I posted? (They do just what you described.) Does that help? *********** Regards, Ron XL2002, WinXP " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum for last calendar month & year
Hi Ron
I must be missing something then because all I get when I use either formula is a Parse Error. Jim Ron Coderre wrote: Um...out of curiosity.... Did you try the formulas I posted? (They do just what you described.) Does that help? *********** Regards, Ron XL2002, WinXP " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sum for last calendar month & year
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 " wrote: Hi Ron I must be missing something then because all I get when I use either formula is a Parse Error. Jim Ron Coderre wrote: Um...out of curiosity.... Did you try the formulas I posted? (They do just what you described.) Does that help? *********** Regards, Ron XL2002, WinXP " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 Ron Coderre wrote: 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 " wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calendar year versus fiscal year | Excel Discussion (Misc queries) | |||
| Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
| Opening calendar form with today's month & year | Excel Discussion (Misc queries) | |||
| change the year in a calendar template to different year | Excel Discussion (Misc queries) | |||
| Calendar Control 11 - Month/Year Only | Excel Programming | |||
Linear Mode
