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

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

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

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