If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to tally a set of values according to month?
Hi everyone,
I am currently working on a spreadsheet involving a list of dates, each with an assigned value. My wish is to add together the values for EACH MONTH, rather than the entire list, spanning a year. This needs to be some kind of function as dates can be added at any point and values can be retrospectively changed. Due to the nature of the spreadsheet I cannot reorder the rows in ascending date order. A colleague suggested that the way forward may be a PivotTable opened in a different sheet, this would be perfect but I can't work out how to pick up data for each month separately! It sounds like there might be a simple solution and I'm certainly hoping there is! Thanks for any feedback Jenni |
#2
|
|||
|
|||
How to tally a set of values according to month?
for a pivot table to work, the input data needs to be appropriate
I would put in two helper columns, one with =Year(date) and the other = Month(date) Depending on the format of your input data, You may actually need a helper sheet where you link to the value and date on your main sheet put in the two helper columns and a pivot table should be easy to make additionally you could use sum product =sumproduct(--(Month(Date_Range)=month_of interest),--(Year(Date_range)=year of interest),Value range) "glitterjen" wrote: Hi everyone, I am currently working on a spreadsheet involving a list of dates, each with an assigned value. My wish is to add together the values for EACH MONTH, rather than the entire list, spanning a year. This needs to be some kind of function as dates can be added at any point and values can be retrospectively changed. Due to the nature of the spreadsheet I cannot reorder the rows in ascending date order. A colleague suggested that the way forward may be a PivotTable opened in a different sheet, this would be perfect but I can't work out how to pick up data for each month separately! It sounds like there might be a simple solution and I'm certainly hoping there is! Thanks for any feedback Jenni |
#3
|
|||
|
|||
How to tally a set of values according to month?
Hi,
I think you mean you have 2 columns Date Value You want to add the values for a given month. If that's correct try:- =SUMPRODUCT((MONTH($A$1:$A$100)=6)*(B1:B100)) Checks A1:A100 and if the month is June(6) add the value in column B. Mike "glitterjen" wrote: Hi everyone, I am currently working on a spreadsheet involving a list of dates, each with an assigned value. My wish is to add together the values for EACH MONTH, rather than the entire list, spanning a year. This needs to be some kind of function as dates can be added at any point and values can be retrospectively changed. Due to the nature of the spreadsheet I cannot reorder the rows in ascending date order. A colleague suggested that the way forward may be a PivotTable opened in a different sheet, this would be perfect but I can't work out how to pick up data for each month separately! It sounds like there might be a simple solution and I'm certainly hoping there is! Thanks for any feedback Jenni |
#4
|
|||
|
|||
How to tally a set of values according to month?
This works great except that if i have nothing in the Date column for a
particular row, but I do in the value row, it automatically adds that value to the January total? "Mike H" wrote: Hi, I think you mean you have 2 columns Date Value You want to add the values for a given month. If that's correct try:- =SUMPRODUCT((MONTH($A$1:$A$100)=6)*(B1:B100)) Checks A1:A100 and if the month is June(6) add the value in column B. Mike "glitterjen" wrote: Hi everyone, I am currently working on a spreadsheet involving a list of dates, each with an assigned value. My wish is to add together the values for EACH MONTH, rather than the entire list, spanning a year. This needs to be some kind of function as dates can be added at any point and values can be retrospectively changed. Due to the nature of the spreadsheet I cannot reorder the rows in ascending date order. A colleague suggested that the way forward may be a PivotTable opened in a different sheet, this would be perfect but I can't work out how to pick up data for each month separately! It sounds like there might be a simple solution and I'm certainly hoping there is! Thanks for any feedback Jenni |
Thread Tools | |
Display Modes | |
|
|