Sum of values up to a specific date (date lower-equal than a value)

[Autista]

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.

[Lupp]

Despite the fact that some localisations mix this up, the terms “Table” and “Sheet” are different.

[Autista]

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

[Lupp]

You mainly need to get the latest date correctly: =EOMONTH(DATE($YYY.$B$2;$YYY.$C$2;1);0). Based on this result the summing of the amounts under the respective condition should be simple with the help of SUMIF(), SUMIFS() or SUMPRODUCT(). (I prefer the last one mostly.)
See also this attached demo.

Editing:
Example attached later by the OQ reworked and attached here again.

Quoting @Autista: “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.”

This is a very bad idea. Calculating with ranges containing blank cells often yields funny results completely incompatible with actual fun. What you want to achieve, e.g. might result in a horrendous error if there is a blank date cell in a row where the respective amount cell was not also made blank (inadvertently?). The ‘Criteria’ concept will result in including blank where a “LTE” (less-than-or-equal) comparison with a nonnegative value is made and so does direct comparison by <= . (Mis-) using spreadsheets for database-like tasks requires to also regard the most fundamental concepts of databases. One of them: “no intermediary rows of any kind”.

Would you mind to enter 1000000 e.g. into cell XXX.E1428 and to look for your partial sums in YYY?

[Autista]

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 send my file with deleted cells containing irrelevant data in a separate answer.

[Lupp]

Did the formulas in the attached example work for you? If so I would suppose you have either the dates or the amounts (or both) as texts. Concerning dates this is a reoccurring poblem with imports from csv, e.g. If the formulas did not,work there must be a problem with the version you are using or one with the way its installed or one with your user profile that might be corrupted.

[Autista]

I doublechecked that. Those are formatted as dates and numbers.
I imported a CSV, but I’m using the dates to filter other stuff and it’s working

[Lupp]

Setting a number format for cells does not afflict texts contained. They remain texts. See attached reworked and my short comments there.
To test for the content type (number, text, or formula) you may use ‘View’>‘Value highlighting’ or -even more clear- the functions ISNUMBER(), ISTEXT(), and ISFORMULA() .
Greetings to “German (Austria)”-country.

[Autista]

Solved,
many thanks for your help from a spanish guy in a “German (Austria)”-country.
I moved the text rows without dates or values to a comment column to remove the empty cells in the data.