Ask Your Question
1

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

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

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

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

endolith gravatar imageendolith ( 2020-05-16 19:26:11 +0200 )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)

erAck gravatar imageerAck ( 2020-05-19 01:53:16 +0200 )edit

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

endolith gravatar imageendolith ( 2020-05-26 01:03:14 +0200 )edit

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

erAck gravatar imageerAck ( 2020-05-26 01:14:05 +0200 )edit

Question Tools

1 follower

Stats

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

Seen: 5,240 times

Last updated: May 06 '17