# Libreoffice Calc Sum of values depending of month and year of a range of dates [closed]

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hello

I'm quite new to Libreoffice and Spreadscheetprograms generally, please keep it in mind.

I have a very large spreadsheet with following data

• Column C - Date in form of DD.MM.YYYY range form 2013 to 2017
• Column E - Numbers (Positive and negative values)

I need to get added all values in column E with following criteria

• for a month (for example 05 - May)
• of a year (for example 2015)
• which are either positive or negative (I will have two formulas, one for the sum of all positive numbers and another for the negatives)

I hope I explained it correctly.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2020-05-19 01:54:02.732110

Sort by » oldest newest most voted

SUMPRODUCT is your friend. Assuming dates in C2:C9 and numbers in E2:E9 this sums values for month May =SUMPRODUCT(MONTH(C2:C9)=5,E2:E9) and this sums values for year 2017 =SUMPRODUCT(YEAR(C2:C9)=2017,E2:E9)

To sum only negative or positive values just add another condition, like =SUMPRODUCT(MONTH(C2:C9)=5,E2:E9<0,E2:E9) for negative values and =SUMPRODUCT(MONTH(C2:C9)=5,E2:E9>0,E2:E9) for positive values.

To sum all positive values in the month May of the year 2017 =SUMPRODUCT(YEAR(C2:C9)=2017,MONTH(C2:C9)=5,E2:E9>0,E2:E9)

more

it works! Thank you very much. I was looking very long for a solution. Now I understand the sumproduct function much better and can make some changes in my own

( 2017-05-08 12:39:18 +0100 )edit

I tried this but it won't work as by date uses a period format DD.MM.YYYY I formatted the cell as date using dots but I still got #VALUE!. I then tried to Substitue the . to a - but it I still get #VALUE!.

( 2017-05-22 21:40:22 +0100 )edit

I solved it, I had to format using the date DD.MM.YYYY and then remove the ' that was added to all my date entries using https://forum.openoffice.org/en/forum...

( 2017-05-22 22:18:02 +0100 )edit

What if you just want to count the number of values meeting the criteria?

( 2020-05-16 19:26:11 +0100 )edit

You just omit the range of data to sum so that the elements are summed/counted where the (products of) condition(s) is(are) true, for example =SUMPRODUCT(MONTH(C2:C9)=5) or =SUMPRODUCT(YEAR(C2:C9)=2017;MONTH(C2:C9)=5)

( 2020-05-19 01:53:16 +0100 )edit

That doesn't work for me, it just says #VALUE!

( 2020-05-26 01:03:14 +0100 )edit

Then probably your date values are not numeric dates but text that looks like dates. See this FAQ.

( 2020-05-26 01:14:05 +0100 )edit