Ask Your Question
0

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

asked 2017-05-04 21:21:57 +0200

this post is marked as community wiki

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.

Thanks in advance

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-05-06 23:02:08 +0200

erAck gravatar image

updated 2017-05-06 23:08:43 +0200

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)

edit flag offensive delete link more

Comments

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

Autista gravatar imageAutista ( 2017-05-08 12:39:18 +0200 )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!.

Quickbudg gravatar imageQuickbudg ( 2017-05-22 21:40:22 +0200 )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...

Quickbudg gravatar imageQuickbudg ( 2017-05-22 22:18:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-04 21:21:57 +0200

Seen: 3,100 times

Last updated: May 06 '17