In Sheet XXX:
- Column C contains dates
- 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:
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.
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.