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

[Autista]

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.

Thanks in advance

[erAck]

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)

[Autista]

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

[Quickbudg]

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

[Quickbudg]

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

[endolith]

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

[erAck]

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)

[endolith]

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

[erAck]

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