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

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.

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

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

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

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 [Solved] Delete apostrophe before numbers in all cells? (View topic) • Apache OpenOffice Community Forum

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

1 Like

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

1 Like

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

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