Ask Your Question

Autista's profile - activity

2019-07-20 12:59:15 +0100 commented question All Links//references between cells in different sheets lost after opening a Libreoffice 5.x file in 6.x

To complete the information I copy here one of the formulas within a Cell referencing to another sheet =SUMPRODUCT(YEAR

2019-07-20 12:50:49 +0100 asked a question All Links//references between cells in different sheets lost after opening a Libreoffice 5.x file in 6.x

All Links//references between cells in different sheets lost after opening a Libreoffice 5.x file in 6.x Hello, I've a

2017-08-17 08:19:52 +0100 received badge  Famous Question (source)
2017-08-03 05:17:19 +0100 received badge  Famous Question (source)
2017-05-22 21:38:59 +0100 received badge  Notable Question (source)
2017-05-09 20:48:20 +0100 received badge  Notable Question (source)
2017-05-09 20:14:53 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 20:12:40 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 20:08:45 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 20:04:55 +0100 commented answer Sum of values up to a specific date (date lower-equal than a value)

Solved, many thanks for your help from a spanish guy in a "German (Austria)"-country. I moved the text rows without date

2017-05-09 20:00:47 +0100 marked best answer Sum of values up to a specific date (date lower-equal than a value)

Hello,

In Sheet XXX:

  • Column C contains dates ($C$1:$C$9999)
  • Column E contains values ($E$1:$E$9999)

in Sheet YYY:

  • B2 contains a year
  • C2 contains a month as number (1 to 12)

In Sheet YYY I need to get the SUM of all values in Sheet XXX - column E up to the last day of the given month and year (if year 2016 and month 05, sum of all values up to the 31.05.2016 included)

I tryed some possibilities with sumproduct and sumif that I found in different forums, but I always get 0,00 or some error as a result.

I also tryed to use a extra cell with the formula to get the date of the last day of the month (see below) but didn't worked: =DATE(B2;C2;DAYSINMONTH(DATE(B2;C2;1)))

I'm trying to do this since hours but can't manage. I konw I'm doing some small mistake, but can't find it. Please have a look at my answer with the uploaded file.


Update: After testing the Answer of Lupp the relust is still 0,00

here is the file I'm working with with all not relevant data deleted.

when looking at the raw data (sheet XXX), there are empty cells. This is because those rows contain text data in other cells but not in the date and value columns.


Update 2: After testing the editing in the Answer of Lupp the results are right :)

I've also removed the empty cells in the sheet XXX by moving the text Data to a new column containing comments and removing the empty rows.

I've also edited the imported Dates from the CSV File with the option "Text to Columns ..." in the Menu "DATA". The imported Dates from the CSV File were actually text, even if the cell format was set to Date. To check for the real format of a value use ISNUMBER(), ISTEXT(), and ISFORMULA() as recommended by LUPP.

2017-05-09 15:15:45 +0100 received badge  Popular Question (source)
2017-05-09 15:09:58 +0100 commented answer Sum of values up to a specific date (date lower-equal than a value)

I doublechecked that. Those are formatted as dates and numbers. I imported a CSV, but I'm using the dates to filter othe

2017-05-09 15:08:48 +0100 commented answer Sum of values up to a specific date (date lower-equal than a value)

I doublechecked that. Those are formatted as dates and numbers

2017-05-09 15:06:46 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 15:06:21 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 15:05:20 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 15:04:43 +0100 edited answer Sum of values up to a specific date (date lower-equal than a value)

After testing the Answer of Lupp the relust is still 0,00 here is the file I'm working with with all not relevant data

2017-05-09 15:04:00 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 15:03:50 +0100 commented question Sum of values up to a specific date (date lower-equal than a value)

sorry. With table is meant sheet. I've corrected the Text.

2017-05-09 15:03:25 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 15:02:38 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 15:02:38 +0100 received badge  Editor (source)
2017-05-09 15:01:38 +0100 edited question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Sheet XXX: Column C contains dates ($C$

2017-05-09 15:00:15 +0100 answered a question Sum of values up to a specific date (date lower-equal than a value)

After testing the Answer of Lupp the relust is still 0,00 here is the file I'm working with with all not relevant data

2017-05-09 14:53:49 +0100 commented answer Sum of values up to a specific date (date lower-equal than a value)

Not working. I now know that I was not wrong with my formulas. I used your formulas (both) and the result is 0,00. I se

2017-05-09 14:51:01 +0100 commented question Sum of values up to a specific date (date lower-equal than a value)

sorry. With table is meant sheet.

2017-05-09 13:13:08 +0100 asked a question Sum of values up to a specific date (date lower-equal than a value)

Sum of values up to a specific date (date lower-equal than a value) Hello, In Table XXX: Column C contains dates ($C$

2017-05-08 12:39:18 +0100 commented answer Libreoffice Calc Sum of values depending of month and year of a range of dates

it works! Thank you very much. I was looking very long for a solution. Now I understand the sumproduct function much bet

2017-05-08 12:38:02 +0100 marked best answer 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.

Thanks in advance

2017-05-08 12:38:02 +0100 received badge  Scholar (source)
2017-05-06 23:34:08 +0100 received badge  Popular Question (source)
2017-05-04 21:21:57 +0100 asked a question Libreoffice Calc Sum of values depending of month and year of a range of dates

Libreoffice Calc Sum of values depending of month and year of a range of dates Hello I'm quite new to Libreoffice and S